Why need to use " for operators in lookup, statistical and power functions if not referencing text

[NattyBer]

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.

[#2]

@NattyBer 

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.

[#3]

@Hans Vogelaar #

Thanks Hans.  I kind of get that but it does seem illogical when the criteria is not a string....or am I misunderstanding?

[#4]

@NattyBer 

Because of the presence of non-numeric characters =, < and/or > in the argument, Excel treats >5 or <=3 as text. 

[#5]

@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.

[#6]

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'.

[#7]

@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.

[#8]

@Joe User 

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...)

[#9]

@Hans Vogelaar  wrote:  ``COUNTIF has been programmed to accept only a range as first argument, not an array of values. [....] I assume that Microsoft doesn't want to change this for reasons of compatibility.``

You're preaching to the choir.

That was my points:  (a) it was an arbitrary choice for reasons that might have made sense decades ago; and (b) the choice was applied to some newer (*) similar functions for consistency (not "compatibility").

(*) "Newer" being a relative term.  Excel 2003 was newer than Excel 95 and Excel 2.0 long before that.  Excel 2010 was the "newest" -- until it was not.)

-----

@Hans Vogelaar  wrote:  ``The new FILTER function [....] does allow criteria such as A1:A10>5, for example =FILTER(B1:B10,A1:A10>5)``

I did not know that.  It is understandable that MSFT would take this opportunity to modernize the new syntax, since they are practically redesigning the Excel language with arcane extensions.

But NattyBer was questioning the "logic" and "inconsistency" of some of the older syntax.

And my primary point was:  there is no "logic" for it.  It is what it is.

You wrote that the COUNTIFS criteria must be text "[b]ecause of the presence of non-numeric characters =, < and/or > in the argument".

But there no "logical" reason for the syntactic difference between AND(H:H>5) and COUNTIF(H:H,">5"), for example, referring to the progenitor of the "...IF(S)" functions.  As I wrote, there is no "logical" reason (*) why the syntax could not have been COUNTIF(H:H>9).

And as you demonstrated, the new FILTER syntax is indeed =FILTER(B1:B10,A1:A10>5).

-----

(*) But the implementor of COUNTIF and SUMIF might have had good reasons that made sense at the time.  In fact, the choice might predate Excel.  I don't remember the details of Visicalc, Lotus 1-2-3 and Multiplan.

[#10]

@Joe User 

I think we basically agree... :)

[#11]

There are some advantages of splitting the criteria into a range and the criterion value as text in that the formula will take arrays as the criterion value without running them through a Lambda helper function.