How do you sum every 5 rows in a column, obtaining the result in the adjacent column?

[samtheman88]

Hello,

I'm working with about 1400 lines in excel: I simply need to sum every 5 rows in a single column, and report the result in the adjacent column. For example, the data I am trying to sum is an column G. I would like to sum G2:G6, G7:G11 etc...and have the result be reported in H1, H2..etc. Any help would be very much appreciated!!!

 

[lancerj017]

are you refering same sheet or the result should be paste on another worksheet?

 

[samtheman88]

On the same work sheet..sorry as you can tell I am no excel wiz.

 

[lancerj017]

another question? what do you prefer a formula or macro? :D

 

[Tom Urtis]

Here’s how you can incrementally sum a range of cells. In the pictured example, each set of five cells in range D2:D26 are summed in range G2:G6.
The formula in cell G2 and copied down to cell G6 is
<CODE>=SUM(OFFSET($D$2,(ROW()-ROW($D$2))*5,0,5,1))</CODE>
You can do this with any incremental count; it need not be 5 cells. For example, if you want to sum each set of 9 cells, your formula would be modified like this:
<CODE>=SUM(OFFSET($D$2,(ROW()-ROW($D$2))*9,0,9,1))</CODE>

I used cells in column D to fit in the picture; change cell references in the formula as needed.

 

[samtheman88]

Formulas, although I will be learning how to use macros soon.

 

[samtheman88]

Wow, thanks, this is a great visual. I'll give it a try! This is a very helpful forum. Thanks so much!

 

[lancerj017]

Or try this macro

Sub SumEvery5Rows()
Dim i As Long, lr As Long
Dim x As Integer, y As Integer
lr = Cells(Rows.Count, "G").End(xlUp).Row
y = 1
For i = 2 To lr Step 5
    y = y + 1
    x = Evaluate("Sum(G" & i & ":G" & i + 4 & ")")
    Range("H" & y).Value = x
Next
End Sub

 

[samtheman88]

Thank you! Got it to work both ways.