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

Answer:

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.

Download excel template file for this tutorial.
how-to-sum-different-rates-with-a-date-criteria
(Excel 97-2003 Workbook *.xls)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array, k) returns the k-th smallest number in this data set.

INDIRECT(ref_text;[a1])
Returns the reference specified by a text string