How do I total a column which was created using the MID function?

[CindyB]

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

 

[JE McGimpsey]

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.

 

[CindyB]

the =-SUM(-cell:cell) results in "#value!"

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.

 

[JE McGimpsey]

Do you have a cell with the #VALUE! error in your range?

 

[Bernard Liengme]

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

 

[Ken Wright]

Can't see why what JE has suggested will not work unless you have text in
the data that cannot be coerced to a number, and that would include any of
your values being returned by the MID function containing a space.

That having been said, I would personally try and fix the data such that
what are meant to be numbers really are numbers, and so whatever formulas
you have returning the values you see, simply precede them all with --
(double negative). Obviously just do one and copy the formulas back down.

You will likely see at least one error, that being a #VALUE error, and that
will be what is killing JEs suggestion for you.

 

[Ragdyer]

Cindy,

I get a #VALUE! error when I *don't* properly enter the formula as an array
formula, using CSE, <Ctrl> <Shift> <Enter>.

If done correctly, XL will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually!
{=-SUM(-B1:B100)

Another alternative, is to convert the *results* of the MID() function to
*true* numbers, thus eliminating the need for an array SUM() formula.

If A1 contained - ABC123DEF
You could try a MID() formula similar to:
=--MID(A1,4,3)
Where the "123" is returned as a *true* XL number, thus allowing a "regular"
SUM() formula to return your totals.