Excel Countif Function Error

I need to count values with < and > infront of them but excel won't recognize them as true numbers. How do I use a count function with true values and values?

Shannon

Posted 2015-02-05T20:10:35.920

Reputation: 1

Welcome to Super User. Could you please edit your question to provide an example of what you are encountering? It would help if you included example values, what value you expect a COUNT formula to return, and what the COUNT formula(s) you've tried returns.

– Excellll – 2015-02-05T20:25:40.683

< and > turn numbers into logical expressions. <5 would be interpreted by Excel as a conditional statement; only include if the value is less than 5, and that would need to make sense and comply with the required syntax for your formula. If you are trying to count occurrences containing "<" or ">", at the least, your target string in the count formula would need to be in quotes. If you are trying to use wildcards, they won't work inside quotes. You also probably need to use COUNTIF. To get more specific help, you will need to provide an example of what you are trying to do. – fixer1234 – 2015-02-05T20:58:54.880

Answers

If you want to use COUNTIF to count text strings like ">5" then that's a little problematic because as per fixer1234's comment, COUNTIF will interpret ">5" as a logical expression.....but if you use an = sign it can work, e.g.

=COUNTIF(A1:A10,"=>5")

Another way is to use SUMPRODUCT where there isn't such confusion, e.g.

=SUMPRODUCT((A1:A10=">5")+0)

barry houdini

Posted 2015-02-05T20:10:35.920

Reputation: 10 434

You have to force Excel to read the content of the cells as text by escaping them with an apostrophe (single quotation).

=COUNTIF(A1:G10,"<5")

user416069

Posted 2015-02-05T20:10:35.920

Reputation: 1