title: Excel - SUM to the end of the list
body: <p>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.</p>
  
  <p>I also display the <code>SUM(X6:X160)</code> and <code>SUBTOTAL(109,X6:X160)</code> 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.</p>
  
  <p><strong>Problem:</strong> 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.</p>
  
  <p>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.</p>
  
  <p><strong>Question:</strong> Is there a way to create SUM and SUBTOTAL from X6 "<strong><em>to the end of column X wherever that is</em></strong>"?</p>
  
  <p><strong>CONCLUSION</strong></p>
  
  <p>I used SUM(OFFSET(....)), taking the "height" parameter from a new field in the header that displays the "number of records" by a <code>=COUNTA($A:$A)-1</code> (-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.</p>