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

[Guest]

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

 

[Guest]

=SUM(O13:O65536)

 

[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

 

[Guest]

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 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 -

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.

 

[Harlan Grove]

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.

....

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))