Another way, entered in B5, for example, sums B1:B4:

=SUM(OFFSET(B5,-4,0,4,1))

"Harlan Grove" wrote:

Quizarate wrote...
I'd like to create a non VBA based formula that will sum up a range that is
offset from the activecell. I tried the following formula to sum a four cell
range:

=SUM(OFFSET(INDIRECT(CELL("address")),-4,0,4,1))

This works fine when I initially enter it, but when numbers in the sum range
change the formula result changes to 0.

Anyone have any ideas of how I can get around this?

Let's say you had {1;2;3;4} in B2:B5 and {9;8;7;6} in C2:C5. If you
enter your formula above in B6, while B6 is the active cell your
formula would return the same result as =SUM(B2:B5). However, if you
move to cell C6 and do anthing that triggers recalculation, your
formula in B6 would return the same result as =SUM(C2:C5).

If you always want the sum of the range of 4 cells immediately above
the cell containing the formula, the simplest way would be to use
INDIRECT with R1C1 references, e.g.,

=SUM(INDIRECT("R[-4]C:R[-1]C",0))