Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on Dates?
I have a column of dates (in calendar order3/1/08, 3/2/08, 3/3/08,etc...) and
a column of corresponding $ amounts next to each date. I need to be able to enter a date in a cell and enter a number of days for instance "5" in another cell and in a third cell return the sum of $ amount associated with the 5 days starting with the date entered. Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on Dates?
=SUMPRODUCT(--(Date_Range=A2),--(Date_Range<A2+B2),Amount_Range)
A2 start date, B2 number of days, if you put 5 in B2 and you want to include the date in A2 as one of the 5 days use the above. If you want 5 days plus the date in A2 change the < to <= -- Regards, Peo Sjoblom "Adam" wrote in message ... I have a column of dates (in calendar order3/1/08, 3/2/08, 3/3/08,etc...) and a column of corresponding $ amounts next to each date. I need to be able to enter a date in a cell and enter a number of days for instance "5" in another cell and in a third cell return the sum of $ amount associated with the 5 days starting with the date entered. Thanks in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on Dates?
Try one of these:
A1:A20 = dates B1:B20 = amounts to sum D1 = start date E1 = number of days =SUMIF(A1:A20,"="&D1,B1:B20)-SUMIF(A1:A20,"="&D1+E1,B1:B20) Format as GENERAL or NUMBER =SUM(OFFSET(B1,MATCH(D1,A1:A20,0)-1,,E1)) -- Biff Microsoft Excel MVP "Adam" wrote in message ... I have a column of dates (in calendar order3/1/08, 3/2/08, 3/3/08,etc...) and a column of corresponding $ amounts next to each date. I need to be able to enter a date in a cell and enter a number of days for instance "5" in another cell and in a third cell return the sum of $ amount associated with the 5 days starting with the date entered. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct with dates | New Users to Excel | |||
sumproduct & dates | Excel Worksheet Functions | |||
SUMPRODUCT - DIFFERENT DATES | Excel Worksheet Functions | |||
sumproduct with dates | Excel Worksheet Functions | |||
sumproduct between dates | Excel Worksheet Functions |