Summary

One of Excel's quirks is that text can sometimes be evaluated as greater than a number, even a large number. In the example shown, the formula in D5 is:

=B5>100

As the formula is copied down, you can see that Excel evaluates text values in D9:D11 as greater than the number 100.

Explanation 

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. 

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.