An easier way to sum amounts for dates ranges is as follows:
Create a small date range table:
A B C
1 Start End $
2 01/Jan/08 31/Dec/08
3 01/Jan/09 31/Dec/09
4 01/Jan/10 31/Dec/10
In this example dates were in column G from rows 3 to 5000
and $ amounts were in column I
Put this formula in cell C2:
Note the $ signs (required for correctly extending the formula by dragging it).
Drag the formula to extend it to the other two date ranges
Here is what I got, and the total in column H was the same as the total of the three date ranges shown.
A B C
1 Start End $
2 01/Jan/08 31/Dec/08 $235,476.89
3 01/Jan/09 31/Dec/09 $241,442.37
4 01/Jan/10 31/Dec/10 $29,296.43
This formula multiplies three things together - note the * signs.
The first thing it does is to look at each cell in the range $G$3:$G$5000 and compares it to the value in cell A2
If the date in any cell in column G is equal to or greater than the date in A2 it returns a value of 1, but if not in this date range it returns a value of zero.
As you know anything x 0 is zero.
So all cells that are less than the date in A2 will be zero
The next part does the same sort of calculation but this time it returns 1 only if the cell has a date less than or equal to the date in Cell B2
Now only cells with a date in the range A2 to B2 (01/Jan/08 to 31/Dec/08) return 1
All other cells return zero.
Finally it takes the value in the cells in column I
It sums the values in all the cells in all the rows 3 to 5000 in column I, after multiplying them by what it has so far which is 1 or zero for each row
So each value in column I is multiplied by 1 or 0 before being summed.
Adjust date ranges to suit.
For months the EOMONTH function is useful as it returns the last day of a month irrespective of the number of days in a month
In the example above =EOMONTH(A4,0) will return 31/Jan/2010, and it works for leap years.
Hopefully slightly clearer than mud.