COUNTIF vs IF function history lesson

[Jerry Atrix]

I know the quotation marks create a text string, and I know the ampersand concatenates two text strings or concatenates a cell reference to a text string, but I’m wondering why <= or >= has to be a text string to work in COUNTIF?

If I do an formula like =IF(B3>0,”YES”,”NO”), this works fine, without having to add special characters (i.e. =IF(B3&”>0”,”YES”,”NO).

But for COUNTIF, a formula like =COUNTIF(B1:B5,>0) won’t work unless you make it =COUNTIF(B1:B5,”>0”).

I understand what to do to get the formulas to work, I guess I’m just confused as to WHY the criteria rules for COUNTIF are different from regular IF.


One of my peers is offering a free lunch to anyone who knows the reasoning and they said the hint is "I am old".

Any insight?!

Thanks!

[shg]

B3>0 is a logical expression that is evaluated and passed to the function as a Boolean

In =COUNTIF(B1:B5, ”>0”), the second argument is passed to the function as string

In =COUNTIF(B1:B5, >0), the second argument is not a valid expression

[Jerry Atrix]

but WHY, was there and older version that was different?

[shg]

Why what?

No difference in versions at least back to Excel 97.

[shg]

In this

=SUMPRODUCT(--(B1:B5>0))

... the B1:B5>0 is a valid expression

Again, >0 standing by itself is not a valid expression.

[satabp]

COUNTIF(range, criteria)


range Required. One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

criteria Required. A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 32, ">32"

In the criteria, if you are providing any expression, that should be provided as a string, this is specially passing due to the consider as a single argument.

[shg]

Maybe you meant why is this not valid:

=COUNTIF(B1:B5>0)

The answer is that all of the *IF functions (COUNTIF, COUNTIF, AVERAGEIF, ...) require a range, not an array, as the first argument.