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 returns the number of items in column H that are greater than 9.
That's fine I guess. But what is the purpose of the " in the above? Why does =COUNTIFS(H:H,>9) not work? It seems a confusing use of " which normally implies text.
NattyBer wrote: ``Why does =COUNTIFS(H:H,>9) not work?``
Because the internal implementation of COUNTIFS is interpreting the conditional expression ">9", not the Excel formula-level evaluation.
The equivalent formula-level expression would be written COUNTIFS(H:H>9) -- one parameter, no comma separator. Why wasn't COUNTIFS designed like that? Only MSFT knows.
Off-hand, I could wild-guess some reasons that might apply to the "...IFS" functions (with "S") per se. But remember: this all began with "...IF" functions (without "S") many decades again. The modern "...IFS" syntax might be chosen simply for consistency.
But that begs the question: why not COUNTIF(H:H>9) many decades ago? Again, only MSFT knows.
And again, I could wild-guess some reasons that might apply decades ago, albeit perhaps not so much today. But my opinion is worth the paper it's written on. (None! wink)
As we used to say in UNIX development: "never ask why". It is what it is.
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.
