Why does COUNTIF return the wrong count for numbers stored as text?
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(--B2:B9>E1))COUNTIF's ">100" skips text. Convert inside the count: =SUMPRODUCT(--(--B2:B9>E1)).
- Result on the example
- 4 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(--(--B2:B9>E1))Result4
B2:B98 rows × 1 col: "150", 85, "220", 40, …A range: 8 cells in one column.
-B2:B9{8 rows × 1 col} -150, -85, -220, -40, …Makes the value negative.
--B2:B9{8 rows × 1 col} 150, 85, 220, 40, …Makes the value negative.
E1100The value in E1.
--B2:B9>E1{8 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The > operator tests greater than.
-(--B2:B9>E1{8 rows × 1 col} -1, 0, -1, 0, …Makes the value negative.
--(--B2:B9>E1{8 rows × 1 col} 1, 0, 1, 0, …Makes the value negative.
SUMPRODUCT(--(--B2:B9>E1))4SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(--B2:B9>E1{8 rows × 1 col} 1, 0, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(--B2:B9>E1)). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(VALUE(B2:B9)>E1)) | 4 | VALUE does the same conversion and reads more clearly. |
| =COUNTIF(B2:B9,">"&E1) | 1Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does COUNTIF return the wrong count for numbers stored as text
E2, count the quantities greater than E1, including the ones stored as text.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.