Forum Discussion
Why need to use " for operators in lookup, statistical and power functions if not referencing text
The criteria argument(s) of COUNTIF, COUNTIFS, SUMIF, etc. are almost always text.
The only exception is when the criteria is just a number value such as 37 or -1.234
Otherwise, you have to specify it as a quoted string.
P.S. If you use a cell reference as criteria, you shouldn't enclose the cell reference in quotes.
Thanks Hans. I kind of get that but it does seem illogical when the criteria is not a string....or am I misunderstanding?
Because of the presence of non-numeric characters =, < and/or > in the argument, Excel treats >5 or <=3 as text.
- Aha! Thanks. I think I get it:
So in this case, the " is telling Excel not to treat as text which is what it is expecting. It does seem inconsistent to me with other formulae where something like >=9 can be used as non text without ". It would perhaps be better if an alternative symbol was used to say 'do not treat as text'.NattyBer wrote: ``the " is telling Excel not to treat as text which is what it is expecting``
Non sequitur! Exactly the opposite: the double-quotes causes ">9" to be treated as text, not a formula-level expression. Internally, COUNTIF(S) is deciding whether to treat the string ">9" as text to be compared or as a conditional expression. It is unfortunate that its interpretation is ambiguous.
Yes, the syntax is inconsistent. Welcome to MSFT's world. Ask yourself: why can we write MAX({1,2,3,4}), but we cannot write COUNTIF({1,2,3,4},">3")? The answer is simple: there is no good reason. It is what it is.
