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'd like to sum all the values in a column starting from a certain minimum value, but I don't want to give an upper bound because any value in the table will be appropriate for summing (minus the header of course) and I'd like to do it the "right" way instead of summing A2:A65535.

Is this possible?

share|improve this question

4 Answers 4

up vote 36 down vote accepted

Just leave off the row numbers:

=SUM(A:A)

Note that this expression cannot be placed in column A (that would cause an endless recursive summation which Excel won't allow).

If you instead wanted to sum all of the rows in column A above the current row (say 51) you could do this:

=SUM(A$1:A50)

If you ever move the cell that holds that expression it would change the A50 to whatever is above the current cell while keeping the starting point of the summation (A1) the same.

The same thing can be done if the total is kept above the summed cells in the current row (say row 1):

=SUM(A2:A$51)
share|improve this answer
1  
So you have to do either an entire column, or both a non-inclusive lower bound with an explicit upper bound? In my case, I'd like to do a mix of the two =SUM(A$1:A) but it doesn't seem to work. In any case, summing the entire column ignores the header and sums my column, so I'll mark this as an answer. Thanks! –  bwerks May 18 '10 at 13:37
    
When summing, text should be ignored so as long as your sum is stored in a different column =SUM(A:A) should work fine. –  Trey Hunner May 18 '10 at 16:05
    
+! thanks for the tips on using dollar sign –  Felipe Alvarez May 26 '11 at 0:12
    
Note that if you have a blank line beneath the header then you will get an error with SUM(A:A). Removing the blank row will get it to work. –  ChrisB May 7 '13 at 10:54
    
yessssssssssssss –  Brock Hensley Jul 23 '13 at 22:44

In my case the fields I didn't want to include in the range actually did have numbers in them. I was stumped until I figured out that you can just sum the entire column and then simply subtract the fields you don't want:

=(SUM($B:$B)-SUM($B$1:$B$6))

Where the first six rows of column B are numbers but not relevant to the data I want summed, and column B contains an indefinite number of rows of information.

share|improve this answer

I used a strange solution:

I placed the sum in the cell that is located at the top-left of the range that I want to sum. Then used merge cells command to merge the sum cell with the one on its right. Result was like this:

enter image description here

share|improve this answer

Yes it is. Please try the following:

=sum(C4:C)

It will sum all rows in C column starting from 4th row

share|improve this answer
    
This answer is incorrect. Excel will not accept the C for the second part of the range reference when the first specifies a specific cell. The reference is illogical: My range is C4 to C what? –  Twisty Nov 22 '14 at 21:43
1  
Sorry. I thought it is about Google Spreadsheets, where it works. My mistake. –  Michal Felski Nov 22 '14 at 22:35
    
Well also this was answered like four years ago. –  bwerks Nov 27 '14 at 1:18

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.