sum column containing both text and numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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)
 
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.
 
You were right - I found that out right after I posted.

Thanks!

Kevin Vaughn said:
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 said:
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?
Click to expand...
Click to expand...
 
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 said:
You were right - I found that out right after I posted.

Thanks!

Kevin Vaughn said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top