Sum contents in column from starting cell on down without setting an explicit last cell index

I have an Excel spreadsheet and would like to sum the numbers in a column starting at one cell (say, B5) and continuing down to the last number in that column.

I know I could just enter a large ending index, like:

SUM(B5:B99999)

But ideally I could just say something like:

SUM(B5:...)

And it would sum from B5 all the way down.

I saw this Superuser question - Excel: is it possible to sum an entire column without setting explicit cell boundaries? - which provides a solution for summing all of the numbers in a column via the following formula:

SUM(B:B)

Problem is, I don't want to sum all of the numbers, just those starting in B5 and down.

Thanks

1

6 Answers

For Excel 2003 or before:

=SUM(B5:INDEX(B5:B65536,MATCH(TRUE,INDEX(ISBLANK(B5:B65536),0,0),0)-1,0))

For Excel 2007 or after:

=SUM(B5:INDEX(B5:B1048576,MATCH(TRUE,INDEX(ISBLANK(B5:B1048576),0,0),0)-1,0))

2

I came up with a hack that works and isn't super ugly...

1. Insert a blank column before B, which will become the new B
2. Merge the B and C cells above the row you want to start the SUM
3. In cell C:1 paste the forumla =SUM(C:C)
4. Hide column B

I don't know why it works, but it does

1

How about

= SUM(B:B) - SUM(B1:B4)

Obviously it won't work if the cell you want your total in is in the B1:B4 range, but it is at least easier to read than Lance's perhaps more proper way.

2

Easiest way is to put the formula like this

"=sum(B5:2)"

This way, you just tell the formula, that you want to start at cell B5 and SUM all values in second column (column B). I know, I'm late with this answer, but just in case somebody faces this issue :)

2

Try this:

sum(B5:B)

It works in Google spreadsheets.

Microsoft support says to do =SUM(BELOW) But it doesn't work for me

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy