# How to SUM values even special characters?

1. ## How to SUM values even special characters?

I like to SUM values ONLY if there is value more than 0. Issue is as I have X and ! but this should be excluded as this will SUM with an error. Is this possible?  Register To Reply

2. ## Re: How to SUM values even special characters?

Can you show us some of your ..numbers..?

Are these something like this?

10!
20x
40
60
50x  Register To Reply

3. ## Re: How to SUM values even special characters?

Sorry for duplicate post. I have more than 100 mails and missed your support.

Yes, this can be:
10!
20x
40
60
50x

or just
!

This sign will block SUM of pure numbers:
40
60  Register To Reply

4. ## Re: How to SUM values even special characters?

ARRAY formula.

=SUM(IF(ISERROR(A1:A10),"",A1:A10))  Register To Reply

5. ## Re: How to SUM values even special characters?

The SUM function should only sum pure numbers and not give errors just because there is text there. Are there errors (#div/0, #NA, #VALUE, etc) in the range you are trying to sum??

Fotis' formula ignores errors within the range which I think is what is going on here.  Register To Reply

6. ## Re: How to SUM values even special characters?

i dont understand! =sum(a1:a6) would give 100 ignoring all the text ones
10!
20x
40
60
50x
!  Register To Reply

7. ## Re: How to SUM values even special characters?

I think what you're trying to do is scrub any values that have text in them to just have the number. If cell is "10x", treat it like 10.
If this is the case, you need to add a scrubbing column to the right of your data and put this equation in and copy down
=VALUE(IF(OR(RIGHT(A2,1)="!",RIGHT(A2,1)="x"),LEFT(A2,LEN(A2)-1),A2))
Now you can do a SUM() of the scrubbed column and get a value of 180. This is assuming the only text values you come across is going to be "x" and "!".  Register To Reply

8. ## Re: How to SUM values even special characters?

well if thats the case
then this array entered formula should do it
=SUM(IF(ISERROR(--SUBSTITUTE(SUBSTITUTE(A1:A10,"!",""),"x","")),0,--SUBSTITUTE(SUBSTITUTE(A1:A10,"!",""),"x","")))  Register To Reply

9. ## Re: How to SUM values even special characters?

@ Martin - I just want to let you know that your solution is beautiful. I'm trying to learn how array formulas work and just spent the last 30 minutes reverse engineering it.  Register To Reply

10. ## Re: How to SUM values even special characters? Originally Posted by martindwilson i dont understand! =sum(a1:a6) would give 100 ignoring all the text ones
10!
20x
40
60
50x
!
No this is not true when I set SUM(). I had also ! or X. This will return SUM with an error. It will not SUM ONLY numbers. It will show an error.

Will function COUNT also react only on numbers?  Register To Reply

11. ## Re: How to SUM values even special characters?

To get the numbers out of a string you can use this one.

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(\$1:\$10000))))

Courtesy of:*Ron Coderre  Register To Reply

12. ## Re: How to SUM values even special characters?

i think you need to post an example sum() ignores text why you say it doesn't mystifies me ,see attached  Register To Reply

13. ## Re: How to SUM values even special characters?

I see it is not easy.
You posted solution and thank you.
=SUM(IF(ISERROR(--SUBSTITUTE(SUBSTITUTE(A1:A10;"!";"");"x";""));0;--SUBSTITUTE(SUBSTITUTE(A1:A10;"!";"");"x";"")))
1. Why is SUM calculated as 40 and not 100?
2. How to SUM also all X and all !  Register To Reply

14. ## Re: How to SUM values even special characters?

not in sample given you get =SUM(A1:A6) =100
and array entered =SUM(IF(ISERROR(--SUBSTITUTE(SUBSTITUTE(A1:A10,"!",""),"x","")),0,--SUBSTITUTE(SUBSTITUTE(A1:A10,"!",""),"x","")))
=180  Register To Reply

15. ## Re: How to SUM values even special characters?

Basically you eliminated special characters and avoid an error when implemented SUM function. Correct?
Can I count only X or !?  Register To Reply

16. ## Re: How to SUM values even special characters?

count =SUMPRODUCT(--ISNUMBER(SEARCH("!",A1:A10)))  Register To Reply