You might need to use an extra column.
Suppose the numbers are in B1:B10. In C1 enter =IF(ISNUMBER(B1*1),B1*1,0)
No use SUM(C1:C10)
You can always insert a new C if needed and then hide it when you have the
SUM in another column
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"CindyB" wrote in message
...
the =-SUM(-cell:cell) results in "#value!"

"JE McGimpsey" wrote:

One way:

Coerce the text to numbers using - (then use - again to turn negative
numbers to positive). Array-enter (CTRL-SHIFT-ENTER, or CMD-RETURN):

=-SUM(-B1:B100)


Note that if you have boolean TRUE values in the range, this will also
coerce those values to 1s.


In article ,
CindyB wrote:

I have a workbook that contains 2 worksheets. The first worksheet is
one
column of text. The second worksheet takes the data from the first
using the
MID function. There are several columns I want to total in the second
worksheet. I've formatted these columns as numbers but the sum function
returns nothing..