LinkBack URL
About LinkBacks
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?
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?
Can you show us some of your ..numbers..?
Are these something like this?
10!
20x
40
60
50x
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
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
ARRAY formula.
=SUM(IF(ISERROR(A1:A10),"",A1:A10))
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.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
i dont understand! =sum(a1:a6) would give 100 ignoring all the text ones
10!
20x
40
60
50x
!
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
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 "!".
Last edited by Craig K.; 06-07-2013 at 09:59 AM.
Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.
Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
If you're happy with an answer given, please click the * under the person's name to boost their reputation.
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","")))
@ 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.
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.Originally Posted by martindwilson
Will function COUNT also react only on numbers?
Last edited by toplisek; 06-08-2013 at 01:38 PM.
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
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
i think you need to post an example sum() ignores text why you say it doesn't mystifies me ,see attached
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 !
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
Basically you eliminated special characters and avoid an error when implemented SUM function. Correct?
Can I count only X or !?
count =SUMPRODUCT(--ISNUMBER(SEARCH("!",A1:A10)))
Last edited by martindwilson; 08-01-2013 at 06:43 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks