As the formula is copied down, you can see that Excel evaluates text values in D9:D11 as greater than the number 100. 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: 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: 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: This formula returns 1 as a result.
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.
