sumproduct with dates for aging

[Jan]

Hi,
Using Excel 2007. When I enter the following formula, I get the correct
answer
=SUMPRODUCT(--((TODAY()-Eagan!$F$4:$F$200>120)),--((TODAY()-Eagan!$F$4:$F$200<900)),--(Eagan!$I$4:$I$200>0),--Eagan!$I$4:$I$200)

But, if I enter this formula as shown below, I get the incorrect answer
=SUMPRODUCT(--((TODAY()-Eagan!$F$4:$F$200>=121)),--(Eagan!$I$4:$I$200>0),--Eagan!$I$4:$I$200).

Since I am at the end column for the aging, I no longer need to have an end
point (or this portion of the formula --((TODAY()-Eagan!$F$4:$F$200<900)).

I can't figure out what is wrong.

Can someone advise?

TIA

 

[Jan]

OOPS...I using Excel 2003

 

[ryguy7272]

Tools > Formula Auditing > Evaluate Formula. That shows you Excel's method
of logic for dealing with every function.

In the first scenario, you are testing for three conditions and then summing
the values in the range Eagan!$I$4:$I$200. In the second you are testing for
two conditions, and then summing the values in range Eagan!$I$4:$I$200.

Now, if you put =TODAY() in Cell F1 and put this:
=F1-SUMPRODUCT(--(Eagan!$F$4:$F$200<900))
in Cell F2

Excel counts from row 4 to row 200, whish is 197 rows, and compares the
values in these rows to see if they are less than 900. I have 121 and 122 in
there, and so all 197 rows are less than 900. If you format F1 and F2 as
General, you will see that F1 is 39902 (means 39902 days have passed since
January 1, 1900, see below for more info.) and from that you subtract 197,
and the result is 39705. Make sense?

See this link for more info.
http://www.meadinkent.co.uk/xlsumproduct.htm

Also, see this:
http://www.cpearson.com/excel/datetime.htm

HTH,
Ryan--