My countif is getting confused with the "<" in the text, and I guess its taking the literal meaning of it.. any way out of it?


Countifs failing

  • Since data in the Col AG are Text so no use of Relational operator < (or count values less than 10000), but it's like Count by match only. – Rajesh S Nov 4 '19 at 6:25

Trying to use certain functions like COUNTIF to compare against a literal value starting with an inequality symbol is a problem because of how the function tries to use the target string.

You have some mismatches between the formula in the question and the one in the screenshot that contribute to the problem (difference in equal sign and cell values that contain a comma), but I'll just address the solution.

There are a few ways you could deal with the inequality symbol, like:

  • store the target string in a cell and compare to the cell: =COUNTIF(AG3:AG8,"="&A1), or
  • replace the symbol in the string with its ASCII character code: =COUNTIF(AG3:AG8,"="&CHAR(60)&"$10,000").

Either would require prefacing the target with an equal sign, which is actually sufficient by itself to be the solution. You were almost there; the formula in the screenshot would have worked without the typo.

When you use a text string as a target, COUNTIF doesn't require an explicit equal sign. So you would typically use something like =COUNTIF(A3:A8,"cat"). However, it also works with an explicit equal sign: =COUNTIF(A3:A8,"=cat").

Using an explicit equal sign works when a text string starts with an inequality symbol. So your formula in the screenshot, but correcting the typo:

  • ,, it's tricky one but is working ,,+1 ☺ – Rajesh S Nov 4 '19 at 6:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.