Another Excel function that works well in this circumstance is SUMPRODUCT()
If your data is in columns A to D, starting at row 2, with dates in column C and you numbers in Column D,
make a results table like this:
F G H I J
2 01-Jan-10 01-Apr-10 01-Jul-10
3 Col A Col B 31-Mar-10 30-Jun-10 30-Sep-10
4 in S 79 45 0
Cells F4 and G4 can use in-cell drop-downs to enable you to select items in columns A and B respectively (Use data validation to create the drop-downs), and have the first and last dates for each quarter at the top.
Put this formula in cell H4:
The formula has been split onto two lines for ease of viewing
Note the $ signs, they are essential for maintaining the correct references when you drag the formula to extend it.
Adjust the end row for your data (in my example the data in columns A to D ended at row 20). Then drag the formula right, under each of the quarters.
You can now change the values in cells F4 and G4 and see the totals for each quarter.
SUMPRODUCT does not have to be entered as an array formula, but it acts as an array formula, working row by row through your data.
PS a quick way to make an end of quarter date from a start of quarter date is to use the EOMONTH() function.
With 01-Jan-2010 in cell H2, put this in H3:
=EOMONTH(H2,2)It returns the end of the month, in this case 2 months after the month in H2. If EOMONTH does not work, make sure that the Analysis Toolpack is enabled.
In Excel 2003 click Tools, Add-Ins... and from the dialog box, find Analysis Toolpack and check the box next to it.
In Excel 2007, Add-Ins are accessed through the Office button, then at the bottom, Excel Options, Then the AddIns Tab, then at the bottom, select Excel Add-Ins in the 'Manage' box, click the 'Go...' button, and then find Analysis Toolpack in the dialog box that opens and check the box next to it, (just one of the ways Excel 2007 is so much easier and more intuitive to use than earlier versions!!!!)