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