# Why is COUNTIF not counting cells with extra spaces?

COUNTIF compares whole cells, so " billing" isn't "billing". Trim inside the count: =SUMPRODUCT(--(TRIM(B2:B9)=E1)).

**Excel:** `=SUMPRODUCT(--(TRIM(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 |
| --- | --- | --- | --- | --- |
| Ticket | Tag |  | Tag | billing |
| T-1 | billing |  | Count |  |
| T-2 | billing |  |  |  |
| T-3 | login |  |  |  |
| T-4 | billing |  |  |  |
| T-5 | shipping |  |  |  |
| T-6 | billing |  |  |  |
| T-7 | login |  |  |  |
| T-8 | billing |  |  |  |

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

Result: 5

## How it works

Some tags were typed or imported with spaces before or after the word, and COUNTIF compares whole cells exactly, so it finds only the 2 clean "billing" cells. `TRIM(B2:B9)` strips the spaces inside the formula, and SUMPRODUCT counts the trimmed tags that match: 5.

## Other ways

- `=COUNTIF(B2:B9,"*"&E1&"*")`: Wildcards on both sides tolerate the spaces, but also count longer tags that contain the word.

## Common mistakes

- TRIM doesn't remove the non-breaking spaces that come from web pages. Add `SUBSTITUTE(B2:B9,CHAR(160),"")` inside.
- COUNTIF ignores case, so case isn't why it misses cells. Spaces and numbers stored as text are the usual causes.

## Questions

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

Helper column `=TRIM(B2)`, then Paste Values over the original.

**How do I count every tag in a list?**

Put the tags in a column and fill `=SUMPRODUCT(--(TRIM($B$2:$B$9)=D2))` down next to them.

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-not-counting-extra-spaces