Forum Discussion
Why need to use " for operators in lookup, statistical and power functions if not referencing text
When using a formula such as COUNTIFS, if I want to evaluate a criteria range against a criteria such as > then I need to put the criteria in quote marks. For example, =COUNTIFS(H:H,">9"). This re...
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'.
