21

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

Community
  • 1
Scott Mitchell
  • 699

6 Answers6

5

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

Steve Mangiameli
  • 196
  • For me, that's the best, easiest and most straight-forward answer to the question, and, thus, should be accepted the accepted answer. PS: In step 3, you can only enter the formular =SUM(C:C) in cell B:1. Recognizing this makes it more obviouos why this works. – BogisW Jan 24 '22 at 21:19
3

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))
Pikamander2
  • 765
Lance Roberts
  • 8,653
  • 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
2

Try this:

sum(B5:B)

It works in Google spreadsheets.

Razvan Tomegea Tommy
  • 21
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 :)

Lad
  • 19
  • 1
    Could you please show a reference to this somewhere? I can't seem to make it work by your description. – Raj Parmar Jan 26 '19 at 23:13
  • 1
    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 07 '19 at 12:07
1

Microsoft support says to do =SUM(BELOW) https://support.microsoft.com/en-us/office/sum-a-column-or-row-of-numbers-in-a-table-2e373a5f-2d8a-478a-9b85-275c8668bebb But it doesn't work for me

Ian Levine
  • 11
  • Welcome to SuperUser! Please do not post link only answers as the linked site might change, rendering it useless. Instead quote the important parts and provide the link as reference only. – DarkDiamond May 18 '22 at 14:33
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – human_rosas May 18 '22 at 14:42
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.

Neal
  • 8,778
  • 1
    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