# 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?

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

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

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

ARRAY formula.

=SUM(IF(ISERROR(A1:A10),"",A1:A10))

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.

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
!

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

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","")))

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.

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?

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

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

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 !

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

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 !?

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1