# Why COUNTIF returns 0 when the value is there

Hidden spaces are the usual cause. Compare trimmed values: `=SUMPRODUCT(--(TRIM(A2:A10)=E1))`.

**Excel:** `=SUMPRODUCT(--(TRIM(A2:A10)=E1))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Customer (CRM export) |  |  | Customer | Contoso |
| Contoso |  |  | Rows |  |
| Fabrikam |  |  |  |  |
| Contoso |  |  |  |  |
| Litware |  |  |  |  |
| Contoso |  |  |  |  |
| Northwind |  |  |  |  |
| Fabrikam |  |  |  |  |
| Adatum |  |  |  |  |
| Proseware |  |  |  |  |

Formula in E2: `=SUMPRODUCT(--(TRIM(A2:A10)=E1))`

Result: 3

## How it works

The export added spaces before or after some names, so "Contoso " is not equal to "Contoso" and `=COUNTIF(A2:A10,E1)` returns 0. TRIM removes leading and trailing spaces from each name before the comparison, and SUMPRODUCT counts the matches: 3.

The lasting fix is to clean the column once (a TRIM helper column, pasted back as values) so every formula works.

## Other ways

- `=COUNTIF(A2:A10,"*"&E1&"*")`: Wildcards tolerate the spaces, but they also count longer names that contain the text, like "Contoso Ltd".
- `=COUNTIF(A2:A10,E1)`: The formula that fails on this data. (Returns 0 because every Contoso has a stray space.)

## Common mistakes

- Text copied from web pages often contains non-breaking spaces (CHAR(160)), which TRIM doesn't remove. Use `TRIM(SUBSTITUTE(A2:A10,CHAR(160)," "))`.
- Numbers stored as text don't match a numeric criteria in some functions. If the column is IDs, check with `=ISTEXT(A2)`.
- A criteria with an operator must be in quotes: `">"&E1`, not `>E1`.

## Questions

**How do I check whether two cells really match?**

Type `=A2=E1` in a spare cell. FALSE means something differs; `=LEN(A2)` shows hidden extra characters.

**Why does COUNTIF return 0 for numbers?**

Usually the criteria is inside the quotes as text, like `">E1"`, or the numbers are stored as text. Fix the quotes or convert the column.

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-returns-zero