Article updated on March 14, 2009

Question: How to sum different rates within a date interval?

Start date: 2008-12-25
End date: 2009-01-06

Rates:
2008-01-01: 99
2008-12-27: 101
2008-12-31: 103
2009-01-04: 105
2009-01-07: 108

Date column: =IF((B9-A9+1)<ROW()-12,"",A9+(ROW(INDIRECT("1:"&(B9-A9+1)))-1)) + Ctrl + Shift + Enter

This formula displays the correct number of dates that are to be summed.
(B9-A9+1) this part of the formula calculates the number of days to display.
A9+(ROW(INDIRECT("1:"&(B9-A9+1)))-1) displays the dates defined in A9 and B9.
The IF function checks that no errors are displayed.

Rate column: =IF((B9-A9+1)<ROW()-12,"",INDEX(B2:B6,MATCH(A9+(ROW(INDIRECT("1:"&(B9-A9+1)))-1),A2:A6,1))) + Ctrl + Shift + Enter

MATCH(A9+(ROW(INDIRECT("1:"&(B9-A9+1)))-1),A2:A6,1) retrieves the correct row number.

INDEX(B2:B6,MATCH(A9+(ROW(INDIRECT("1:"&(B9-A9+1)))-1),A2:A6,1)) gets the correct rate. The IF function checks that no errors are displayed.

Sum cell: =SUM(IF(ISERROR(B13:B37),0,B13:B37)) + Ctrl + Shift + Enter sums only values, no errors.

how-to-sum-different-rates-with-a-date-criteria
(Excel 97-2003 Workbook *.xls)