8

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

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))
  • 1
    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
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.

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

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

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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.