Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with dates for aging
Hi,
Using Excel 2007. When I enter the following formula, I get the correct answer. =SUMPRODUCT(--((TODAY()-Eagan!$F$4:$F$200120)),--((TODAY()-Eagan!$F$4:$F$200<900)),--(Eagan!$I$4:$I$2000),--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$2000),--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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with dates for aging
OOPS...I using Excel 2003
"Jan" wrote: Hi, Using Excel 2007. When I enter the following formula, I get the correct answer. =SUMPRODUCT(--((TODAY()-Eagan!$F$4:$F$200120)),--((TODAY()-Eagan!$F$4:$F$200<900)),--(Eagan!$I$4:$I$2000),--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$2000),--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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with dates for aging
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-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jan" wrote: Hi, Using Excel 2007. When I enter the following formula, I get the correct answer. =SUMPRODUCT(--((TODAY()-Eagan!$F$4:$F$200120)),--((TODAY()-Eagan!$F$4:$F$200<900)),--(Eagan!$I$4:$I$2000),--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$2000),--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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Aging formula with unknown dates | Excel Worksheet Functions | |||
aging days between dates | Excel Discussion (Misc queries) | |||
Sumproduct using dates | Excel Worksheet Functions | |||
sumproduct between two dates | Excel Worksheet Functions | |||
SUMPRODUCT ON DATES | Excel Worksheet Functions |