Why does '=A1>0' return TRUE, but '=COUNTIF(A1, ">0")' returns 0 for a cell containing text?

I am getting inconsistent behavior in Microsoft Excel 2021 when comparing cells with text to numbers.

When I do a direct comparison like =A1 > 0 or =IF(A1 > 0, TRUE, FALSE), Excel returns TRUE when A1 contains text like "NULL" or "Test". However, when I use =COUNTIF(A1, ">0"), Excel returns 0, as if the expression evaluated to FALSE.

Example:

Why does Excel treat text values as greater than 0 except when using COUNTIF? Shouldn't COUNTIF count any expression that evaluates to TRUE?

To answer your question:

The comparison operators work differently in the two instances.

A1>0: If the value in A1 is not a number, Excel first tries to coerce that value to a number. And when it does that, Excel treats strings as being greater than zero.

COUNTIF(A1,">0"): Here excel does not attempt to coerce the value in range to a number. It ignores the value in A1 completely if it is not a number.

If A1 contained 1 as a text string, COUNTIF would likewise ignore it.

To have the formula count the 1 as a text string, you would have to ensure the criteria is looking for a text string greater than 0: =COUNTIF(A1,">""0""")

Of course, this latter will ignore numerical values in A1. And also you should be aware that the comparison is a lexicographical comparison. For example, it would consider "2" to be greater than "101"

So if you wanted to use COUNTIF and have it behave similarly to the direct comparison A1>0, you would need to combine the formulas: =COUNTIF(A1,">""0""") + COUNTIF(A1,">0")

Another solution is using SUMPRODUCT as in the answer of @Ike