# Why does COUNTIF return the wrong count for numbers stored as text?

COUNTIF's ">100" skips text. Convert inside the count: =SUMPRODUCT(--(--B2:B9>E1)).

**Excel:** `=SUMPRODUCT(--(--B2:B9>E1))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Order | Qty |  | Over | 100 |
| O-1 | 150 |  | Count |  |
| O-2 | 85 |  |  |  |
| O-3 | 220 |  |  |  |
| O-4 | 40 |  |  |  |
| O-5 | 99 |  |  |  |
| O-6 | 310 |  |  |  |
| O-7 | 101 |  |  |  |
| O-8 | 64 |  |  |  |

Formula in E2: `=SUMPRODUCT(--(--B2:B9>E1))`

Result: 4

## How it works

Some quantities were imported as text. COUNTIF with ">100" counts only real numbers, so it skips "150", "220" and "101". The inner double minus converts every quantity to a number, the comparison runs on those, and SUMPRODUCT counts the TRUEs: 4.

## Other ways

- `=SUMPRODUCT(--(VALUE(B2:B9)>E1))`: VALUE does the same conversion and reads more clearly.
- `=COUNTIF(B2:B9,">"&E1)`: The plain COUNTIF, which skips quantities stored as text. (Counts 1: only 310 is a real number above 100.)

## Common mistakes

- Text that isn't a number at all, like "n/a", makes the conversion return #VALUE!.
- Comparing text with a number directly, as in `=B6>100`, is TRUE for any text, so "99" looks bigger than 100.

## Questions

**How do I convert the column for good?**

Select it and use Data, Text to Columns, Finish, or the warning icon's Convert to Number.

**Does SUMIF have the same problem?**

Yes. It skips text numbers in the sum range and in comparisons.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/countif-numbers-stored-as-text