Summing Quarters
 

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

Hi,
Let's say your information is in sheet2 and you want the Q in sheet 1 so
enter the formula as follow

=SUM(OFFSET(INDEX(Sheet2!$1:$1,1,(COLUMN()*3)-2),,,,3))

hope this helps


"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

Assuming data is in row 2
=sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in
Column D, Column E, Column F

"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

Thanks, David, but I'm trying to find a way to automate this. Assuming data
for the first year is in A1..L1, I want quarterly totals in A2..D2.
"David" wrote:

Assuming data is in row 2
=sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in
Column D, Column E, Column F

"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

It depends on where you want to put the info..
do you want to put the information on another worksheet or on the same
worksheet? also do you want to put the info at the end of all of those
columns or do you want to do put it at the end...in between add a column
between c and d then put a = sign in the column next to the 3 boxes you want
to add, then the reference numbers to those boxes example =a1+b1+c1 press
enter and the box will show the answer. You can then select the box with
your mouse and you will highlight the box (note the box will have a dark
black small box in the bottom right hand corner) grab that little box and
drag to end of column where info is to be added... it will automatically
populate boxes.. let me know if this helps... let me know if you have any
questions

"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

are you wanting a macro?
Sub Macro1()
'
' Macro1 Macro
'

'
Range("A2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-1]C[2])"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[2]:R[-1]C[4])"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[4]:R[-1]C[6])"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[6]:R[-1]C[8])"
Range("D3").Select
End Sub

"Jeff" wrote:

Thanks, David, but I'm trying to find a way to automate this. Assuming data
for the first year is in A1..L1, I want quarterly totals in A2..D2.
"David" wrote:

Assuming data is in row 2
=sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in
Column D, Column E, Column F

"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

Hi,
in A2 enter

=SUM(OFFSET(INDEX(1:1,1,(COLUMN()*3)-2),,,,3))

"Jeff" wrote:

Thanks, David, but I'm trying to find a way to automate this. Assuming data
for the first year is in A1..L1, I want quarterly totals in A2..D2.
"David" wrote:

Assuming data is in row 2
=sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in
Column D, Column E, Column F

"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

Eduardo - I double-checked that I copied your formula accurately, but the
answer comes back as 0.

"Eduardo" wrote:

Hi,
in A2 enter

=SUM(OFFSET(INDEX(1:1,1,(COLUMN()*3)-2),,,,3))

"Jeff" wrote:

Thanks, David, but I'm trying to find a way to automate this. Assuming data
for the first year is in A1..L1, I want quarterly totals in A2..D2.
"David" wrote:

Assuming data is in row 2
=sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in
Column D, Column E, Column F

"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

Alex - Data is in Row 1, so monthly data for year 1 is in A1..L1. Right now,
I'm typing sum(a1.c1) in A2, sum(d1.f1) in B2, etc. I want to automate this.

"Alex" wrote:

It depends on where you want to put the info..
do you want to put the information on another worksheet or on the same
worksheet? also do you want to put the info at the end of all of those
columns or do you want to do put it at the end...in between add a column
between c and d then put a = sign in the column next to the 3 boxes you want
to add, then the reference numbers to those boxes example =a1+b1+c1 press
enter and the box will show the answer. You can then select the box with
your mouse and you will highlight the box (note the box will have a dark
black small box in the bottom right hand corner) grab that little box and
drag to end of column where info is to be added... it will automatically
populate boxes.. let me know if this helps... let me know if you have any
questions

"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

sorry jeff scratch that i see what you want to do... simply you can enter
=a1+b1+c1+d1 in A2 and that should do it automatically.. and then in in b2
put =e1+f1+g1+h1 and so on ... these will automatically poplulate as numbers
enter your boxes a1thru l1

"Alex" wrote:

It depends on where you want to put the info..
do you want to put the information on another worksheet or on the same
worksheet? also do you want to put the info at the end of all of those
columns or do you want to do put it at the end...in between add a column
between c and d then put a = sign in the column next to the 3 boxes you want
to add, then the reference numbers to those boxes example =a1+b1+c1 press
enter and the box will show the answer. You can then select the box with
your mouse and you will highlight the box (note the box will have a dark
black small box in the bottom right hand corner) grab that little box and
drag to end of column where info is to be added... it will automatically
populate boxes.. let me know if this helps... let me know if you have any
questions

"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.

Summing Quarters
 

put a equals sign in from of sum and put a ":" between a1:c1

"Jeff" wrote:

Alex - Data is in Row 1, so monthly data for year 1 is in A1..L1. Right now,
I'm typing sum(a1.c1) in A2, sum(d1.f1) in B2, etc. I want to automate this.

"Alex" wrote:

It depends on where you want to put the info..
do you want to put the information on another worksheet or on the same
worksheet? also do you want to put the info at the end of all of those
columns or do you want to do put it at the end...in between add a column
between c and d then put a = sign in the column next to the 3 boxes you want
to add, then the reference numbers to those boxes example =a1+b1+c1 press
enter and the box will show the answer. You can then select the box with
your mouse and you will highlight the box (note the box will have a dark
black small box in the bottom right hand corner) grab that little box and
drag to end of column where info is to be added... it will automatically
populate boxes.. let me know if this helps... let me know if you have any
questions

"Jeff" wrote:

Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing
this manually, but there must be a better way. Thanks.