Sum up a column from a specific row down

Question

It seem simple but I cannot find a way to define a range that goes up to the end of the column in an Excel formula.

For instance I can use this equation SUM(C:C) to sum all number found on the column C. However, given that the top of the page has titles and column headers, I would like to start my range at line 6. I thought SUM(C6:C) would do it but it does not work in Excel.

This is required so my SUM is always right no matter how many lines of data I add to my document in the future.

Thanks.

If all that is above the C6 is text anyways, text will not be summed with the rest of the column as the values need to be numeric. — user2140261
The thing is that I cannot guaranty what will be the content above because the file may be used by different people and even I may forget over time that I cannot put numbers above. — jmbouffard

user2140261 user2140261 · Accepted Answer · 2014-01-22T15:05:59

=Sum(C:C)-Sum(C1:C5)

Sum everything then remove the sum of the values in the cells you don't want, no Volatile Offset's, Indirect's, or Array's needed.

Just for fun if you don't like that method you could also use:

=SUM($C$6:INDEX($C:$C,MATCH(9.99999999999999E+307,$C:$C))

The above formula will Sum only from C6 through the last cell in C:C where a match of a number is found. This is also non-volatile, but I believe more costly and sloppy. Just added it in case you'd prefer this anyways.

If you would like to do function like CountA for text using the last text value in a column you could use.

=COUNTIF(C6:INDEX($C:$C,MATCH(REPT("Z",255),$C:$C)),"T")

you could also use other combinations like:

=Sum($C$6:$C$65536)

or

=CountIF($C$6:$C$65536,"T")

The above would do what you ask in Excel 2003 and lower

=Sum($C$6:$C$1048576)

or

=CountIF($C$6:$C$1048576,"T")

Would both work for Excel 2007+

All above functions would simply ignore all the blank values under the last value.

Sum up a column from a specific row down

5 Answers