# 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

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))
``````
• It still looks like you're just going to the maximum ending index, 65536. If I know this column is going to be all numbers from `B5` downward, I could just enter `SUM(B5:B65536)`. My question, though, is whether it's possible not to specify a terminating index when supplying a starting index. It sounds like this may not be possible. – Scott Mitchell Apr 28 '11 at 19:38
• @Scott, No, I'm going to the first cell before the blank in the range B5:B65536. See the ISBLANK function. – Lance Roberts Apr 28 '11 at 20:12

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

• The problem is that I don't have numbers in cells `B1:B4`. Moreover, I want this summation to appear in cell B1. If I put the formula `SUM(B:B)` in cell `B1` then Excel gives me a circular reference error message and shows the sum as 0. – Scott Mitchell Apr 28 '11 at 19:37
• @Scott: The circular reference thing was why I said it wouldn't work if your formula was in the range B1:B4. I wouldn't worry about not having numbers in B1:B4, Excel should just ignore the cells when doing the sum. I was just trying to avoid having the big number for the number of rows. – Neal Apr 28 '11 at 19:48

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

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

• Could you please show a reference to this somewhere? I can't seem to make it work by your description. – Raj Parmar Jan 26 at 23:13
• hi, I've checked this and you'r right, it stopped working this way - unfortunately. Needed to rewrite all my formulas using this :( – Lad Mar 7 at 12:07