From Monthly to Quarterly Numbers

[jgorman]

I have many years of by month data listed in columns. I would like t
start a new sheet that adds 3 months of data into a quarterly column
That is it would add the the first 3 months of a year for Q1, then tak
months 4,5,6, for Quarter 2, months 7,8,9 for Q3 etc. and be able t
move the data to the new sheet automaticaly. Any ideas muc
appreciated.
Thanks
Jak

 

[Guest]

Hi,
For results as tabulated below on Sheet2:

Year Q1 Q2 Q3 Q4
2005 158 66 213 189
2006 155 209 167 143
2007 163 0 0 0

Source has dates in column A and data in Column C on Sheet1. Change
ranges/sheets to suit.

In cell for Q1:

=SUMPRODUCT(--(MONTH(Sheet1!$A$1:$A$100)>=1),--(MONTH(Sheet1!$A$1:$A$100)<=3),--(YEAR(Sheet1!$A$1:$A$100)=$A2),--(Sheet1!$C$1:$C$100))

or

=SUMPRODUCT--(MONTH(Sheet1!$A$1:$A$100)<=3),--(YEAR(Sheet1!$A$1:$A$100)=$A2),--(Sheet1!$C$1:$C$100))

In Cell for Q2:

=SUMPRODUCT(--(MONTH(Sheet1!$A$1:$A$100)>=4),--(MONTH(Sheet1!$A$1:$A$100)<=6),--(YEAR(Sheet1!$A$1:$A$100)=$A2),--(Sheet1!$C$1:$C$100))


In Cell for Q3:

=SUMPRODUCT(--(MONTH(Sheet1!$A$1:$A$100)>=7),--(MONTH(Sheet1!$A$1:$A$100)<=9),--(YEAR(Sheet1!$A$1:$A$100)=$A2),--(Sheet1!$C$1:$C$100))


In Cell for Q4:

=SUMPRODUCT(--(MONTH(Sheet1!$A$1:$A$100)>=10),--(YEAR(Sheet1!$A$1:$A$100)=$A2),--(Sheet1!$C$1:$C$100))

Copy down.

HTH