Forum Discussion
Why need to use " for operators in lookup, statistical and power functions if not referencing text
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.
The new FILTER function (available in Microsoft 365 and Office 2021) does allow criteria such as A1:A10>5, for example
=FILTER(B1:B10,A1:A10>5)
will return the values from B1:B10 for which the corresponding cells in A1:A10 are greater than 5.
COUNTIF has been programmed to accept only a range as first argument, not an array of values. Similarly SUMIF only accepts ranges as first and third arguments. I assume that Microsoft doesn't want to change this for reasons of compatibility. Hence the same restrictions hold for COUNTIFS, SUMIFS, AVERAGEIF, AVERAGEIFS and MAXIFS (and there is no MAXIF...)
