Excel 2007 : Sum Quarters

OK, spent all day trying to figure out how to do this and still can't get it to work.

Column B is a column of Dates, say Jan 2010 to Dec 2060 at monthly intervals

Column F is the £ income value for each month.

Cell A1 allows the user to enter any date

I need a formula to look at the date in Cell A1 and then sum the total income for the appropriate quarter that this date falls in (assumes standard year quarters of Mar, Jun, Sept and Dec), so for example if the user enters April 4th 2012 in Cell A1, this falls into the second quarter, so my formula needs to total the income from Column F for April, May and June 2012.

I have no problem identifying the quarter that the date falls in using

=ROUNDUP(MONTH($A$1)/3,0)

but I am struggling to get my formula to identify all the months that fall in the quarter and then sum the income for just those months from Column F. Every variation I've tried returns zero.

I've tried variations on SUMPRODUCT and SUMIFS:

=SUMPRODUCT(--($B$1:$B$360=YEAR($A$1)),--($B$1:$B$360=ROUNDUP(MONTH($A$1)/3,0)),--($F$1:$F$360))

YEAR($A$1) identifies the year correctly, ROUNDUP(MONTH($A$1)/3,0), identifies the correct quarter but I realise that this maybe where my formula is not right.

Can anyone please help as I can't figure this one out.

Ultimately I am looking to use the formula to calculate income for the current quarter (based on the date entered in A1), the previous quarter and following quarter, which will all be variations on the same formula...

Many thanks

Try

=SUMPRODUCT(--(YEAR($B$1:$B$360)=YEAR($A$1)),--(MONTH($B$1:$B$360)=ROUNDUP(MONTH($A$1)/3,0)),$F$1:$F$360),

although if you are always summing 3 amounts you might be able to do that like this, assuming your dates in column B are always 1st of the month

=SUM(OFFSET($F$1,MATCH(DATE(YEAR($A$1),FLOOR(MONTH($A$1)-1,3)+1,1),$B$1:$B$360,0)-1,0,3))

Hi daddylonglegs

Have adapted your SUMPRODUCT version because the year in cell A1 (now E1 in the attached sample) needs to consider what happens if the next quarter falls into the following year, but the year in your formula always resolves to the year in E1 and also the total comes to zero. For example, if the date entered in E1 is in December, the resulting income total would need to be for the first 3 months of the following year. I have attached a sample (a stripped down version of my spreadsheet, so the column references have changed). So I now have

=SUMPRODUCT(--(YEAR($B$2:$B$101)=YEAR(EDATE($E$1,3)))--(ROUNDUP(MONTH($B$2:$B$101)/3,0)=ROUNDUP(MONTH(EDATE($E$1,3))/3,0)),--($C$2:$C$101))

This is giving me a result, but not the right result. In fact it is totalling all entries for the specified year plus all the entries for the specified month in column C and then adding the amount from the single cell that matches the specified month and year on top. I have evaluated the formula and I can't see any errors at any stage, so still unsure where I am going wrong.

I tried the offset formula and am currently getting a N/A error. I think the SUMPRODUCT option is close, but just need to figure out the error.

Let me know if you have any further thoughts or if you can spot the error?

Many thanks, very grateful for your help

OK, just spotted my error - missing commas, all working now.

=SUMPRODUCT(--(YEAR($B$2:$B$101)=YEAR(EDATE($E$1,3))),--(ROUNDUP(MONTH($B$2:$B$101)/3,0)=ROUNDUP(MONTH(EDATE($E$1,3))/3,0)),--($C$2:$C$101))

Many thanks daddylonglegs...