COUNTIF says 2, the sheet shows 4 (hidden spaces)
The problem
Problem statement
Four reps are classed as East in A2:A6, but a plain COUNTIF only finds two of them: the export left trailing spaces and one lower-case entry. Put the real East count in the East count cell D2.
Do not edit the region column — clean it inside the formula.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | |
|---|---|---|---|
| 1 | Region | Rep | East count |
| 2 | East | Ada | |
| 3 | East | Ben | |
| 4 | West | Cleo | |
| 5 | East | Dan | |
| 6 | east | Eli |
Notes
COUNTIF matches text exactly (case-insensitively) but a trailing space makes "East " a different string from "East". TRIM inside SUMPRODUCT normalises each cell before comparing, and the = comparison ignores case. COUNTIF's wildcard route ("east*") also works and is shorter, but it matches any text starting with east, not east exactly.
How to approach
Your answer goes in D2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is D2
- Look at A2 — there is a space after East
- TRIM every cell before comparing to "East"
- Shape: =SUMPRODUCT(--(TRIM(?) = ?))
Official solutions
Show 2 solutions — try it yourself first
Approach 1: trim and compare as an array
=SUMPRODUCT(--(TRIM($A$2:$A$6) = "East"))
TRIM removes the trailing space from every cell, the comparison produces TRUE/FALSE, and the double unary minus turns those into 1s. Four rows match, including the lower-case one, because = does not care about case.
Approach 2: COUNTIF with a wildcard
=COUNTIF($A$2:$A$6, "east*")
The asterisk absorbs whatever follows "east", so trailing spaces stop mattering. Shorter, but it would also count "Eastern" — fine here, dangerous in a region list that contains one.
Function guide
FAQ
Why does COUNTIF return 0 for a value I can see?
The stored string differs from what you typed — usually a trailing space, a non-breaking space, or a number stored as text. TRIM it, or match with a wildcard.
Does Excel compare text case-sensitively?
No: COUNTIF and = ignore case, so "east" matches "East". Use EXACT when case must matter.