Summing a rolling range of cells

[Guest]

How do I sum the adjacent 4 columns into a cell? I want an cell to always sum
the 4 cells to the left, allowing for me inserting a new column each week,
ie. a rolling answer.

Any help greatly appreciated.

Thanks

 

[Guest]

Use a mixed reference in your SUM function: =SUM($C2:F2), if columns C to F
are your 4 columns, always insert a new row before the last data column
(i.e., your 3rd column with 4 columns of data, the 4th column with 5 columns
of data etc.) and then you AutoFill the formula down to the remaining rows in
the last column...I am assuming you are summing.

The alternative is VBA where you make Excel look for a new column to the
left of the SUM column and then programatically change the range argument of
the SUM function in the 5th, 6th etc. column where you are summing.

 

[Dave Peterson]

If the cell that contains the formula is E1, then one way:
=SUM(OFFSET(E1,0,-4,1,4))

 

[Guest]

Offset assumes you know how many columns there are going to be. You will have
to change it every time you insert a column which at that point, you may as
well reselect the SUM function range as it will be faster to do.

 

[Guest]

Consider this UDF:

Function zum() As Variant
Application.Volatile
With Application.Caller
zum = .Offset(0, -4) + .Offset(0, -3) + .Offset(0, -2) + .Offset(0, -1)
End With
End Function

It will always sum the four cells to the immediate left of the cell
containing the function.

 

[Dave Peterson]

The OP said that he wanted to always sum 4 columns to the left.

I don't understand your comment.