How to Count Cells with Text in Excel

Count text with COUNTIF

The asterisk wildcard represents any sequence of characters, so =COUNTIF(A2:A100,"*") counts cells that hold text. It does not count ordinary numbers, dates stored as numeric serials, logical values, errors, or truly empty cells. This is usually the right answer for a column that mixes labels and numbers.

To count text containing a word, use a pattern such as =COUNTIF(A2:A100,"*pending*"). Wildcards are not case-sensitive. To count a literal asterisk, escape it with a tilde: =COUNTIF(A2:A100,"~*"). Define whether partial matches are acceptable before choosing the pattern.

For an exact label, drop the wildcards: =COUNTIF(A2:A100,"Open"). To count everything except a label, =COUNTIF(A2:A100,"<>Open") also counts blank cells, so subtract COUNTBLANK(A2:A100) when blanks should not be part of the answer. When several conditions apply, move to COUNTIFS and keep every range the same size, for example a status column and a region column.

Know when COUNTA is different

=COUNTA(A2:A100) counts every nonempty value: text, numbers, dates, errors, TRUE/FALSE, and formulas that return an empty string. It answers “how many cells contain something?” rather than “how many cells contain text?” COUNT counts numeric values only, so neither COUNT nor COUNTA replaces the text wildcard when the column is mixed.

A cell that looks blank may contain a formula returning "", a space, or an apostrophe. A formula result of "" is text, so both COUNTIF(range,"*") and COUNTA count it. To count only text with at least one character, use =COUNTIF(A2:A100,"?*"). Inspect suspicious cells with LEN before building a dashboard metric.

Use SUMPRODUCT for explicit text tests

For an explicit type check, =SUMPRODUCT(--ISTEXT(A2:A100)) counts values for which ISTEXT is TRUE. This pattern is useful when you want the intent to be unmistakable or need to combine the type test with other conditions. Keep ranges bounded; entire-column array calculations can be expensive in large workbooks.

If spaces should not count as meaningful text, test them separately, for example with a helper column =LEN(TRIM(A2))>0. TRIM removes ordinary spaces but may not remove every imported nonbreaking character, so fixing the source is usually better than compensating in every summary formula.

Common mistakes

COUNTIF with "*" is not a count of nonblank cells, and COUNTA is not a text-only count. Dates normally count as numbers even though they display like text, and numbers stored as text count as text. Because a stable formula applied to unstable data gives unstable totals, check the source types when a count changes after an import. Compare the text count with COUNTA and COUNT on the same range: text plus numbers plus logical values should equal the nonblank total, and any gap points to errors or unusual cells worth inspecting before the figure goes into a report.