Excel - SUM to the end of the list

Question

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"?

CONCLUSION

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.

Answer 1

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

Answer 2

Using Offset() and Count() seems to be the most popular, and I'm sure the most efficient, to use.

=SUM(OFFSET($X$6,0,0,COUNT($X$6:$X$1000)))

Personally, I tend to use Indirect() a lot for things. It will probably run slower but it works. It helps me to see the range that is being created. Careful though, since part of the range is held in text, it will not update when you move the formula around. That can trip you up. Here you are anyway.

=SUM(INDIRECT("$X$6:$X$" & COUNT($X$6:$X$1000)))

You can use the dynamic ranges that iDevlop points out or put the Offset() or Indirect() inside the Sum() like I just did.

Either way you want to be careful because Indirect() and Offset() are Volatile Functions. Which generally you want to avoid.

I also found this guy, who uses Index() and Match(), which are not volatile functions.

EDIT:

Thinking of it (because I just did on a spreadsheet of mine)..

Provided you know your data will have a reasonable limit, say 1000, you can just use =SUM($X$6:$X$1000) and it will skip the blanks, even for Subtotal method 1 or 101 (average).

Answer 3

Sure ! And I find it much better to have the total at the top, since you can also freeze the first row(s) to keep those totals visible.
Just ask Google for "Excel dynamic range". Here are a few links:
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://support.microsoft.com/kb/830287

It's all done with a combination of Offset() and Count() functions.