Quarter Calculation

[jerrymoon1]

I have an input spreadsheet that I have monthly data. Row 2 has the month and year - C3 has Jan 05 all the way through AL with Dec 2007. Data is on rows 3 to row 1057. Is there a way to have a formula add the months for a quarter using a date entered in cell B1? For example if I enter

Apr 07 in cell D2 it would add data from cells AD3 and put it in cell CT3
May 07 in cell D2 and it would add cells AD3+AE3 and put it in cell CT3
Jun 07 in cell D2 and it would add cells AD3+AE3+AF3 and put it in cell CT3
Jul 07 in cell D2 and it would add cells AG3and put it in cell CT3....etc.

Would like to be able to do this for the entire range from Jan 05 - Dec 07.

Thanks for your assistance.

 

[jerrymoon1]

Sorry I posted after your response Barry.

Tried your second one and it works. Would you mind explaining how the formula works. Might be able to adapt this for other things I do.

Thanks again!!

 

[Gerald Higgins]

OK, so why don't you use the idea of totals suggested several posts back ?

In one single row, you could have a string of totals that add up the relevant months.

At the top of the April column, have a formula that just sums up the April budget data.
At the top of the May column, have a formula that sums up the April and May budget data.
At the top of the June column, have a formula that sums up the April, May and June budget data.
For example, if April is in col A, May in col B, June in Col C, here are the formulas to put at the top of Col A -

=sum(A2:A1000)

for Col B -

=sum(A2:B1000)

and for Col C -

=sum(A2:C1000)

and edit for the length of your columns as required.

All these formulas should sit on the same row. Then, you can copy them across in blocks, as suggested by Expiry earlier on.
Once you've done this, use hlookup to cross reference these formulas with your input month.

If this doesn't work, please please try uploading a SAMPLE of your data - you don't need to upload the whole spreadsheet.

 

[jerrymoon1]

Barry - how would I modify the formula to calculate a YTD number, versus having 12 columns with a calculated ytd - such as YTD Jan 07, YTD Feb 07,...etc? These type of formulas helps to cut down the size of my excel files.

 

[barry houdini]

Perhaps it would be simpler to use SUMPRODUCT

=SUMPRODUCT(--(C$2:AL$2>=DATE(YEAR(B$1),1,1)),--(C$2:AL$2<=B$1),C3:AL3)

To sum the YTD where your date to sum to is in B1. You could also use that method for your original problem, i.e. to sum for the QTD

=SUMPRODUCT(--(C$2:AL$2>=DATE(YEAR(B$1),FLOOR(MONTH(B$1)-1,3)+1,1)),--(C$2:AL$2<=B$1),C3:AL3)