sum column containing both text and numbers
 

I need a formula that will sum a column that contains both text and numbers.
The text and numbers appear in this column as the result of an if formula (if
value returned 0, "-", [value]). Can I sum a column containing mixed
information?

sum column containing both text and numbers
 

Maybe

=SUM(IF(LEFT(A1:A6,1)="-",-MID(A1:A6,2,99),A1:A6))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cbuck" wrote in message
...
I need a formula that will sum a column that contains both text and
numbers.
The text and numbers appear in this column as the result of an if formula
(if
value returned 0, "-", [value]). Can I sum a column containing mixed
information?

sum column containing both text and numbers
 

Use the sum function. It will ignore text values and sum just the numbers.
If there are errors though, it will return the error value.
--
Kevin Vaughn


"cbuck" wrote:

I need a formula that will sum a column that contains both text and numbers.
The text and numbers appear in this column as the result of an if formula (if
value returned 0, "-", [value]). Can I sum a column containing mixed
information?

sum column containing both text and numbers
 

You were right - I found that out right after I posted.

Thanks!

"Kevin Vaughn" wrote:

Use the sum function. It will ignore text values and sum just the numbers.
If there are errors though, it will return the error value.
--
Kevin Vaughn


"cbuck" wrote:

I need a formula that will sum a column that contains both text and numbers.
The text and numbers appear in this column as the result of an if formula (if
value returned 0, "-", [value]). Can I sum a column containing mixed
information?

sum column containing both text and numbers
 

You're welcome. That frequently happens (where you figure out the answer
right after you post. Or you remember some more information that you should
have included in the post.)

--
Kevin Vaughn


"cbuck" wrote:

You were right - I found that out right after I posted.

Thanks!

"Kevin Vaughn" wrote:

Use the sum function. It will ignore text values and sum just the numbers.
If there are errors though, it will return the error value.
--
Kevin Vaughn


"cbuck" wrote:

I need a formula that will sum a column that contains both text and numbers.
The text and numbers appear in this column as the result of an if formula (if
value returned 0, "-", [value]). Can I sum a column containing mixed
information?