Hello all

If I have a table of data with multiple columns each representing a
month, is there a quick way to create a linked table with subtotals by
quarter, or by year.

So for example
A1=Jan08, A2=Feb08, A3=Mar08, A4=Apr08, A5=May08, A6=Jun08

I would like to sum in another table
A1+A2+A3 ("Quarter1")then in the next cell A4+A5+A6 ("Quarter 2")

Obviously this is easy but when you are dealing with several years it
is fiddly. Is there a quick formula I can enter to instruct excel to
add 3 columns together starting from the next one along from the last
set of 3?

Equally, it would be good to be able to quickly and easily subtotal
the years from groups of 12 columns.

Oman,

What you are describing is a built-in function of Pivot Tables, termed "Grouping".

See http://www.contextures.com/xlPivot07.html

HTH,
Bernie
MS Excel MVP


"Oman" wrote in message
...
Hello all

If I have a table of data with multiple columns each representing a
month, is there a quick way to create a linked table with subtotals by
quarter, or by year.

So for example
A1=Jan08, A2=Feb08, A3=Mar08, A4=Apr08, A5=May08, A6=Jun08

I would like to sum in another table
A1+A2+A3 ("Quarter1")then in the next cell A4+A5+A6 ("Quarter 2")

Obviously this is easy but when you are dealing with several years it
is fiddly. Is there a quick formula I can enter to instruct excel to
add 3 columns together starting from the next one along from the last
set of 3?

Equally, it would be good to be able to quickly and easily subtotal
the years from groups of 12 columns.

Thanks Bernie - my table isn't a pivot table.

One way around it is to add the quarters at the end of each set of 3
months, so:-

A1=Jan08, A2=Feb08, A3=Mar08, A4=Q1 and so on

but the trouble with this is that the formulae that feed the row data
for the months then also need to be amended for the quarter columns.

Oman

Oman,

Your table may not be a pivot table, but it can be the source of the data for a pivot table. Select
the table, then use Data / Pivot Table... etc.

HTH,
Bernie
MS Excel MVP


"Oman" wrote in message
...
Thanks Bernie - my table isn't a pivot table.

One way around it is to add the quarters at the end of each set of 3
months, so:-

A1=Jan08, A2=Feb08, A3=Mar08, A4=Q1 and so on

but the trouble with this is that the formulae that feed the row data
for the months then also need to be amended for the quarter columns.

Oman

Hi,

First you say you have multiple columns representing the months, and then
you show us data with multiple rows representing months?

For this example I wil assume your data is by months vertically. Let's
suppose there are titles on the first row and the data starts in A2 and goes
down.

Then enter the formula anywhere and copy it down 3 more rows and over for as
many columns as you want: This will return the quarterly totals.

=SUM(OFFSET($A$1,1+(ROW(A1)-1)*3,0,3))

If this helps, please click the Yes button.

--
Thanks,
Shane Devenshire


"Oman" wrote:

Hello all

If I have a table of data with multiple columns each representing a
month, is there a quick way to create a linked table with subtotals by
quarter, or by year.

So for example
A1=Jan08, A2=Feb08, A3=Mar08, A4=Apr08, A5=May08, A6=Jun08

I would like to sum in another table
A1+A2+A3 ("Quarter1")then in the next cell A4+A5+A6 ("Quarter 2")

Obviously this is easy but when you are dealing with several years it
is fiddly. Is there a quick formula I can enter to instruct excel to
add 3 columns together starting from the next one along from the last
set of 3?

Equally, it would be good to be able to quickly and easily subtotal
the years from groups of 12 columns.