[excel] Sum up a column from a specific row down

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.

This question is related to excel excel-formula

The answer is


This seems like the easiest (but not most robust) way to me. Simply compute the sum from row 6 to the maximum allowed row number, as specified by Excel. According to this site, the maximum is currently 1048576, so the following should work for you:

=sum(c6:c1048576)

For more robust solutions, see the other answers.


You all seem to love complication. Just click on column(to select entire column), press and hold CTRL and click on cells that you want to exclude(C1 to C5 in you case). Now you have selected entire column C (right to the end of sheet) without starting cells. All you have to do now is to rightclick and "Define Name" for your selection(ex. asdf ). In formula you use SUM(asdf). And now you're done. Good luck

Allways find the easyest way ;)


If you don't mind using OFFSET(), which is a volatile function that recalculates everytime a cell is changed, then this is a good solution that is both dynamic and reusable:

=OFFSET($COL:$COL, ROW(), 1, 1048576 - ROW(), 1)

where $COL is the letter of the column you are going to operate upon, and ROW() is the row function that dynamically selects the same row as the cell containing this formula. You could also replace the ROW() function with a static number ($ROW).

=OFFSET($COL:$COL, $ROW, 1, 1048576 - $ROW, 1)

You could further clean up the formula by defining a named constant for the 1048576 as 'maxRows'. This can be done in the 'Define Name' menu of the Formulas tab.

=OFFSET($COL:$COL, $ROW, 1, maxRows - $ROW, 1)

A quick example: to Sum from C6 to the end of column C, you could do:

=SUM(OFFSET(C:C, 6, 1, maxRows - 6, 1))

or =SUM(OFFSET(C:C, ROW(), 1, maxRows - ROW(),1))


Something like this worked for me (references columns C and D from the row 8 till the end of the columns, in Excel 2013 if relevant):

=SUMIFS(INDIRECT(ADDRESS(ROW(D$8), COLUMN())&":"&ADDRESS(ROWS($C:$C), COLUMN())),INDIRECT("C$8:C"&ROWS($C:$C)),$C$2)