Excel - SUM to the end of the list

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

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).

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.