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

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

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

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

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

In article ,
CindyB wrote:

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

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

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"CindyB" wrote in message
...
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..

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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