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

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