If Excel COUNTIF trailing spaces are causing a low status total, do not rewrite the formula first. Test one suspected status against a clean copy. A value that displays as Open may actually be Open with an extra character at the end.
Quick answer: If Excel COUNTIF is not counting a status that looks right, test the suspected cell with EXACT before changing the count formula. With A1:A3 containing Open, Open (with one trailing space), and Closed, =COUNTIF(A1:A3,"Open") returns 1 because the second Open contains an extra character.
Why COUNTIF misses a status that looks right
COUNTIF counts cells that meet its criterion. For text values, an ordinary trailing space makes the stored value different from the criterion "Open". This often appears after a CSV export, copy and paste, or a system download.
Microsoft notes that leading and trailing spaces, nonprinting characters, and inconsistent quotation marks can cause unexpected COUNTIF results. Its COUNTIF guidance also confirms that text criteria are not case-sensitive.
Check Excel COUNTIF trailing spaces in three cells
- Put the status values in column A and type a known-clean
Openin column B. - Count the list with
=COUNTIF(A1:A3,"Open"). - Compare the visible match and the suspected mismatch with
EXACT.
Worked input and output
Enter Open in A1, Open followed by one trailing space in A2, and Closed in A3. Enter Open in B1 and B2 as the clean comparison status.
| Cell | Value or formula | Result |
|---|---|---|
| A1 | Open | Clean status |
| A2 | Open followed by one trailing space | Looks like Open |
| A3 | Closed | Different status |
| B1 | Open | Clean comparison value |
| B2 | Open | Clean comparison value |
| C1 | =COUNTIF(A1:A3,"Open") |
1 |
| C2 | =EXACT(A1,B1) |
TRUE |
| C3 | =EXACT(A2,B2) |
FALSE |
In C1, enter =COUNTIF(A1:A3,"Open"). In C2, enter =EXACT(A1,B1), and in C3, enter =EXACT(A2,B2).
C1 returns 1, C2 returns TRUE, and C3 returns FALSE. The count is low because A2 is not an exact text match for the criterion.
EXACT compares two text strings character by character, including spaces and letter case. Microsoft’s EXACT function reference shows that it returns FALSE when otherwise similar text contains a space difference.
Separate whitespace from a case-only difference
COUNTIF ignores letter case: Open, OPEN, and open all satisfy =COUNTIF(A1:A3,"Open"). A case-only difference is therefore not the reason for a low COUNTIF result, while =EXACT("Open","OPEN") returns FALSE.
A cell containing Open followed by a trailing space does not satisfy the Open criterion. =EXACT(A2,B2) returns FALSE when A2 is Open plus one trailing space and B2 is Open, even though both values can look identical on screen.
If no cells contain an exact Open value, =COUNTIF(A1:A3,"Open") returns 0. Use EXACT against a known clean status to distinguish an absent status from a hidden-character mismatch.
Clean the values only when that is the intended fix
When ordinary spaces are the issue, create a helper column rather than overwriting the import immediately. In D1, enter =TRIM(A1) and fill down. Then count the helper values, for example =COUNTIF(D1:D3,"Open"). Keeping the original beside the cleaned value makes the reconciliation easier to audit.
TRIM removes ordinary leading, trailing, and repeated spaces, but it may not remove every nonprinting or nonbreaking character imported from another system. If the cleaned count is still wrong, inspect the import for nonprinting characters and consider CLEAN as Microsoft recommends.
For a broader refresher on criteria-based counts, see this Excel SUMIF and COUNTIF guide. If the problem is a status mismatch between two exports rather than a single-list count, use a structured approach to reconcile two Excel lists.
Compatibility and limits
COUNTIF and EXACT are available in Excel for Microsoft 365, Excel for the web, Excel 2024, Excel 2021, Excel 2019, and earlier desktop versions. If the source data must be cleaned, use a helper column with =TRIM(A2), then count the cleaned values.
This diagnostic is for text equality. It does not tell you which invisible character is present, and it does not make COUNTIF case-sensitive. Use EXACT to prove a difference, then clean or correct the source only after confirming what the business status should be.
Sources
Practice with the exact example data
Use a separate blank worksheet for each example. Enter each value at its listed cell address, then type the formula in AA1. Quoted text is shown in JSON notation to preserve spaces: enter the characters inside the quotes, without the quotes, using a leading apostrophe to keep text and spaces literal. An empty quoted string means an explicit empty text value; use an apostrophe entry where appropriate. Compare your result with the expected result below. These are synthetic practice data.
Example 1 — Main case
| Cell | Input value |
|---|---|
| A1 | "Open" |
| A2 | "Open" |
| A3 | "Closed" |
Formula in AA1: =COUNTIF(A1:A3,"Open")
Expected result: 2
Example 2 — Boundary case
| Cell | Input value |
|---|---|
| A1 | "Closed" |
| A2 | "Pending" |
| A3 | "Hold" |
Formula in AA1: =COUNTIF(A1:A3,"Open")
Expected result: 0
These examples were checked with a limited calculator supporting numeric aggregates, ASCII text functions, exact criteria and exact first-match XLOOKUP. Only the displayed examples and supported semantics were calculated. They were not executed in Microsoft Excel. This check does not verify every claim in the article.
Prepared with automated drafting and review, official references, and a limited example calculator. Synthetic examples are provided for practice; no independent human review or Microsoft Excel execution is claimed.