Sum or count date then amount

[pgarcia]

Hello all,
I need to look up a name "Susan" in A1, sum $ in B1 but only if C1=2/28/2009
is greater then 7 days. I would need to also do the following: 7 or more days
(which is the example), 3-6 days old and 1-2 days old.

Thanks

 

[Sheeloo]

Use
=SUMPRODUCT(--(A1:A100="Susan"),--(C1:C100="2/28/2009"),(B1:B100))
or
=SUMPRODUCT(--(A1:A100="Susan"),--(C1:C100=Today()),(B1:B100))

Change 100 to your last row

You can add other conditions by adding terms like
--(C1:C100>"2/28/2009")
--(C1:C100<"2/28/2009")
--(C1:C100=(Today()+7)

 

[francis]

try the Sumproduct
=SUMPRODUCT(--(A1:A10="Susan"),--(C1:C10<DATE(2009,2,28)+7),--(B1:B10))

Change the 2nd part for the day to +6, +2 according.

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[pgarcia]

I'm getting 0. Any thoughts? I'm using Excel 2009. I have notice what
SUMPRODUCT does not work as it should in older versions.

 

[Shane Devenshire]

Hi,

Your question doesn't make sense - C1 = 2/28/2009 is greater than 7? what
does that mean? In Excel every date after 1/7/1900 is greater than 7!

If you are asking to count or sum the number of items in column C which are
more than 7 days old relative to TODAY then there are your formulas:

SUM:
=SUMPRODUCT(--(A1:A16="Susan"),--(TODAY()-C1:C16>7),B1:B16)

COUNT:
=SUMPRODUCT(--(A1:A16="Susan"),--(TODAY()-C1:C16>7))

You may choose to modify the >7 portion depending on whether you include
today in your definition of one day gone.

 

[Shane Devenshire]

Hi,

Where did you get Excel 2009? Are you testing beta of the next version?
The is an Excel 2008 for the Mac but the latest version for the PC is 2007.
In all versions, Excel 2009 excluded, SUMPRODUCT works in the same way. If
it is returning 0 and you are using the formulas I submitted, then it
suggests that the dates may not be legal Excel dates they may be text entries
for example.

 

[Shane Devenshire]

Hi Francis,

No need for the -- in front of the (B1:B10) assuming that the contents of
column B are numbers and not numbers entered as text. And if they are number
then you can drop the parens. If they are text numbers then --B1:B10 is
sufficient.

The general reason for using -- is to convert the results of a text from
{TRUE,FALSE,TRUE...} to {1,0,1,...} so that calculations can be performed.