Sum and Offset: to add from a point to the last entry in the colum

A

Ailish

Hi All

I am using Excel 2003, I want to put a formula on my spreadsheet in O9 that
will sum from O13 through to the last value in the column which changes all
the time. I have tried using sum and offset but I just can't seem to get it
to work.

Help please.
Ailish
 
B

Bernard Liengme

Why not =SUM(O13:O65536)
It does not matter that cells after the last one are blank
Or am I missing something?
best wishes
 
A

Ailish

I justed wanted to ensure that if the person wanted to add some summary
analysis under the block that the formulae would still work on just that
contiguous block.

Any ideas,

Thanks
Ailish
 
S

S Davis

I justed wanted to ensure that if the person wanted to add some summary
analysis under the block that the formulae would still work on just that
contiguous block.

Any ideas,

Thanks
Ailish






- Show quoted text -
Click to expand...

You would need the user to right click -> insert down and then type in
the value. That would expand the boundaries of the summary range.
 
H

Harlan Grove

Ailish said:
I justed wanted to ensure that if the person wanted to add some
summary analysis under the block that the formulae would still
work on just that contiguous block.
Click to expand...
....

This is a change from your original specs. Are you assuming that this
other person would leave at least one blank row between the data block
and the summary entries? Should such summary entries be excluded from
the sum? If so, try the array formula

=SUM(O13:INDEX(O13:O65536,MATCH(FALSE,ISNUMBER(O13:O65536),0)))

This avoids volatile functions, like OFFSET. But if you want to use
OFFSET, try the array formula

=SUM(OFFSET(O13,0,0,MATCH(FALSE,ISNUMBER(O13:O65536),0),1))
 
Top