Sum to Blank Cell

[steveal]

I want to sum a series of columns of different lengths.
It may be just 2 rows deep (i.e. sum B3 and B4 with answer in B5) or 20 rows
deep (i.e. sum B3:B22 with answer in B23). Above each column is an empty
cell

How can I say "sum the column above until you reach an empty cell"?

Thanks,

Steve

 

[Teethless mama]

Try this:

=SUM($B$3:OFFSET(B5,-1,))

 

[smartin]

I want to sum a series of columns of different lengths.
It may be just 2 rows deep (i.e. sum B3 and B4 with answer in B5) or 20 rows
deep (i.e. sum B3:B22 with answer in B23). Above each column is an empty
cell

How can I say "sum the column above until you reach an empty cell"?

Thanks,

Hi Steve,

Would it be acceptable to put all the sums against a fixed row range in
one row, e.g. this in b100:

=sum(b3:b99)

or is there some reason you need to break at a blank cell?

 

[steveal]

The summation I want to do is actually in one column (I wrote my example for
simplicity)

So I want to sum
B3:B5 answer in B6
B8:B15 answer in B16
B18:B35 answer in B36

Etc.

Each column of numbers a different length.

Steve

 

[smartin]

Thanks for the clarification. I see your challenge is more complicated
than I think can be handled with a worksheet function (but I could be
wrong!)

This sounds like a job for VBA. Excel VBA is not my forte, so I do not
have a solution. If you do not get an answer here, you might try posting
your (clarified) question in microsoft.public.excel.programming.

Best of luck!

 

[steveal]

smartin,

I'm afraid I chickened out and spent a few hours doing the job 'manually'.
Sometimes I ask the question here and a tremendous labour saving answer
appears - sometimes not...

Thanks for your help.

Steve