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

User · Accepted Answer

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

User · Answer

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

User · Answer

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    amp     amp ADDRESS ROWS  C  C   COLUMN     INDIRECT  C 8 C  amp ROWS  C  C    C 2

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to excel-formula