Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
See even this answer –  Hastur Jun 26 at 7:56

2 Answers 2

up vote 0 down vote accepted

Here's one way:

=SUM(B5:INDEX(B5:B65536,MATCH(TRUE,INDEX(ISBLANK(B5:B65536),0,0),0)-1,0))
share|improve this answer
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

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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