a twist to relative ranges

[Ray Pendergast]

I'm trying to write a function that will copy from one row to the next one
down, but move the target range a greater distance. Here's the scenario.

A1=10
A2=20
A3=30
A4=40
A5=50
A6=60
A7=70
A8=80
A9=90
A10=100
A11=110
A12=120

B1=SUM(A1:A3) = 60
B2=SUM(A4:A6) = 150
B3=SUM(A7:A9) = 240
B4=SUM(A10:A12) = 330

Do you see how the target ranges in column B skip? I'm only going down one
row, but I want the data collected to move down by three rows. My question
is this: Is there a way to write the function in cell B1 so that I can copy
it and paste it to cell B2 and on down without having to re-write the range
of cells in the parentheses?

Thank you!

 

[Gord Dibben]

Ray

In B1 enter =SUM(OFFSET($A$1,ROW()*3-3,0,3,1))

Drag/copy down to B4


Gord Dibben MS Excel MVP

 

[Bob Phillips]

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JMB]

You could try:

=SUM(OFFSET(B1,ROW()-ROW($B$1),-1,3))

change B1 to whatever cell you enter the formula in.

 

[JMB]

Somewhere along the way the *2 got left out. Also, after seeing others
suggestions, changed it to offset from A1 so the column offset doesn't have
to be hardcoded.

=SUM(OFFSET(A1,(ROW()-ROW($B$1))*2,0,3))

 

[Ragdyer]

Another way ... non-volatile,
Can be entered anywhere, and copied down to total every 3 rows of the column
referenced in the Index() portion of the formula:

=SUM(INDEX(A:A,3*ROWS($1:1)-2):INDEX(A:A,3*ROWS($1:1)))