Excel – SUM to the end of the list


I have a database-like table in Excel 2003, with a single header row containing AutoFilters in row 6, and data from row 7 to -say- row 160. Rows 1 – 5 are the sheet title, column group titles and instructions I cannot get rid of.

I also display the SUM(X6:X160) and SUBTOTAL(109,X6:X160) in this area for numeric columns. I don't want to display this at the bottom of the table because several users add rows to that table frequently and they destroyed the formulas on a regular basis.

Problem: whenever a user adds data to the end of the table, one would need to update the SUM and SUBTOTAL formulas to expand the range which – needless to say – is mostly forgotten.

I could myself extend the formulas to cover all rows up to -say- row 500 and hide the remaining rows, so a user would need to "insert" rows when the visible end of the table is reached – which in turn would update the formulas, but I don't regard this as a very safe way … knowing my users.

Question: Is there a way to create SUM and SUBTOTAL from X6 "to the end of column X wherever that is"?


I used SUM(OFFSET(….)), taking the "height" parameter from a new field in the header that displays the "number of records" by a =COUNTA($A:$A)-1 (-1 for the column heading text that is counted as well), as it is clear from the business context that a key value must exist in column A for any valid data record and no blank rows are allowed (I can train users that much at least) – plus the user benefit of not only seeing the SUM but now as well the COUNT of records in the header frozen pane.

Best Solution

Well i think you can use something like =SUM(C:C) to sum all the cells in column C and it will exclude the text automatically i tried it and it worked

Related Question