Excel troubleshooting

Why Is COUNTIF Not Counting Text?

Fix COUNTIF text criteria issues caused by hidden spaces, wildcard syntax, case expectations, and imported data cleanup problems.

Problem

  • COUNTIF may miss text values when the visible text contains hidden spaces, inconsistent punctuation, or unexpected characters.

Quick fix

  • Clean the source data with TRIM and CLEAN, then use exact text criteria or wildcards such as "*text*".

Text counting pitfalls

  • COUNTIF is not case-sensitive, so case is usually not the problem.
  • Leading or trailing spaces make text values different even when they look the same.
  • Wildcards must be inside quotes, such as \
  • .

Best fixes

  • Use a helper column with TRIM(CLEAN(cell)) for imported text.
  • Use COUNTIF(range, \
  • ) for exact text or COUNTIF(range, \
  • ) for contains text.
  • Use COUNTIFS when text must match alongside another condition.

Frequently asked questions

  • Does COUNTIF care about uppercase? No — COUNTIF is case-insensitive. Look for spaces, wildcards, or criteria not wrapped in quotes.
  • How do I count cells containing part of a word? =COUNTIF(range,"*apple*") with asterisks inside the quoted criteria.
  • COUNTIF vs COUNTA for non-blank cells? COUNTA counts any non-empty cell. COUNTIF counts cells matching your rule — use COUNTA for blanks only.