If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
Sumproduct on Dates?
=SUMPRODUCT(--(Date_Range=A2),--(Date_RangeA2+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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|