Copy Sum formula to different row but use same column data

[Guest]

I have the following data and am trying to copy the sum function to create a
running total. When I copy the formula it keeps the same column and I need
it to jump to the subsequent column. Assume this data is in columns A and B
starting in A1.

1 5 =sum(A1:A4)
2 6 =sum(b1:B4) This is what I want to happen but when I
copy the
3 7 formula in C1 to C2 it keeps the
formula in the A
4 8 column and just switches the
range. Is there any
way to have it jump to the
next column without having
to manually change it?

 

[Guest]

One way ..

In C1:
=SUM(OFFSET($A$1:$A$4,,ROW(A1)-1))
Copy down

 

[Roger Govier]

Hi Max

Just a quick "heads up".
If the OP copies that formula into C1 and copies down then they will get
a circular reference when they get to C3.
If it is only the first 4 rows of data being summed, then entering the
formula in any column, row 5 or greater will be OK.

As an alternative, they could also consider the non-volatile
=SUM(INDEX($1:$4,1,ROW(A1)):INDEX($1:$4,4,ROW(A1)))

again entered in row 5 or below.

 

[Max]

Thanks Roger. Yes, it was careless of me not to have noticed.