Summing Quarters

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

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.
 
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
 
Assuming data is in row 2
=sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in
Column D, Column E, Column F
 
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.
 
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
 
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
 
Eduardo - I double-checked that I copied your formula accurately, but the
answer comes back as 0.
 
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.
 
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
 
Back
Top