Text is greater than number

Excel's formula engine has some quirks that you should be aware of. One of these quirks is that Excel will treat a text value as larger than a number by default. For example:

=90>100 // returns FALSE
="A">100 // returns TRUE

The second formula above returns TRUE when you probably expect it to return FALSE. You can see this behavior in the worksheet shown in cells D9:D11. We are comparing each value in column B to 100, and the values in these cells return TRUE because they contain text. Essentially, any text value (even a space " ") will be evaluated as greater than any number.

Counting values greater than

This behavior can affect how other formulas count values that are greater than a specific number. For example, the COUNTIF and COUNTIFS functions don't exhibit this behavior. The formula in G5 returns 1:

=COUNTIF(B5:B11,">100") // returns 1

However a formula that deals with logical expressions directly will show Excel's native behavior. For example, the SUMPRODUCT formula in cell F7 returns 4:

=SUMPRODUCT(--(B5:B11>100)) // returns 4

This is an example of using Boolean logic in a formula.

Ignoring text values

To ignore text values in a formula like this, you can add an additional check with the ISNUMBER function. The SUMPRODUCT formula below has been modified to check that values are (1) larger than 100 and (2) actually numeric:

=SUMPRODUCT(--(B5:B11>100)*ISNUMBER(B5:B11)) // returns 1

This formula returns 1 as a result.