From Monthly to Quarterly Numbers

[jgorman]

I have many years of by month data listed in columns. I would like to 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 take months 4,5,6, for Quarter 2, months 7,8,9 for Q3 etc. and be able to move the data to the new sheet automaticaly. Any ideas much appreciated.
Thanks
Jake

[Toppers]

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


"jgorman" wrote:

>
> I have many years of by month data listed in columns. I would like to
> 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 take
> months 4,5,6, for Quarter 2, months 7,8,9 for Q3 etc. and be able to
> move the data to the new sheet automaticaly. Any ideas much
> appreciated.
> Thanks
> Jake
>
>
> --
> jgorman
> ------------------------------------------------------------------------
> jgorman's Profile: http://www.excelforum.com/member.php...o&userid=33081
> View this thread: http://www.excelforum.com/showthread...hreadid=528933
>
>