Excel guide · 2 min read

How to count cells with text in Excel

Use =COUNTIF(A2:A10,"*"). The asterisk wildcard matches any text, so it counts every cell holding text and skips numbers, dates and empty cells.

Sample data

Every formula on this page was calculated live against this sheet.

	A	B	C
1	Customer	Status	Ref
2	Acme Ltd	Paid	1042
3	Brightside	Overdue	PO-77
4	Coral & Co		1043
5	Delta Foods	Paid	PO-81
6
7	Evergreen	Part paid	1050
8	Fernhill	Overdue	n/a
9	Granite Inc	Paid

Count all text cells with COUNTIF

"*" means "any sequence of characters", so COUNTIF(range,"*") counts cells that contain text. The empty row 6 is ignored. In real Excel, numbers and dates are skipped too, even though they are not empty.

fx=COUNTIF(A2:A10,"*")

Result: 7

Returns 7 customer names; the blank rows are not counted.

Count text in a column that also has numbers

The Ref column mixes numeric invoice numbers with text references. COUNTA counts every non-empty cell and COUNT counts only numbers, so the difference is the number of text cells. =COUNTIF(C2:C10,"*") gives the same answer in Excel.

fx=COUNTA(C2:C10)-COUNT(C2:C10)

Result: 3

Returns 3: PO-77, PO-81 and n/a.

Count cells with specific text

Put the exact word in quotes to count an exact match. It ignores case, so "paid" and "Paid" are the same. This does not count "Part paid", because the whole cell must match.

fx=COUNTIF(B2:B10,"Paid")

Result: 3

Returns 3 invoices marked Paid.

Count cells that contain a word

Wrap the word in asterisks to count cells containing it anywhere. "*paid*" picks up Paid and Part paid. Use "PO-*" to count cells that start with a prefix.

fx=COUNTIF(B2:B10,"*paid*")

Result: 4

Returns 4: three Paid plus one Part paid.

Count blank status cells

Often the real question is "how many are missing?" COUNTBLANK counts empty cells in a range. Coral & Co has no status and row 6 is empty, so the count is 3 including row 10.

fx=COUNTBLANK(B2:B10)

Result: 3

Returns 3 empty Status cells in B2:B10.

Common mistakes

Formulas that return empty text

A formula like =IF(A2="","",A2) looks blank but still counts for COUNTA. Count real values with a condition instead, such as COUNTIF(range,"?*"), which needs at least one character.

Using COUNT for text

COUNT only counts numbers and returns 0 for a column of names. Use COUNTIF(range,"*") or COUNTA.

Cells with just a space are counted

A stray space is text, so COUNTIF "*" and COUNTA both count it. Clean the data with TRIM or Find and Replace.

Numbers stored as text are counted

Imported IDs like 1042 stored as text count as text. If they should be numbers, convert them with Data > Text to Columns > Finish.

FAQ

What formula counts cells with text in Excel?

=COUNTIF(range,"*"). It counts any cell that contains text and skips numbers, dates and empty cells.

What is the difference between COUNTA and COUNTIF "*"?

COUNTA counts every non-empty cell, including numbers, dates and errors. COUNTIF with "*" counts text only.

How do I count cells that are not blank?

Use =COUNTA(range), or =COUNTIF(range,"<>"). Both count text and numbers.

How do I count distinct text values?

In Excel 365, use =COUNTA(UNIQUE(range)). See the count unique values guide for older versions.

How do I count cells that contain a specific word?

Wrap the word in asterisks: =COUNTIF(B2:B10,"*paid*"). It matches the word anywhere in the cell and ignores case.

How do I count cells with text and a second condition?

Use COUNTIFS, such as =COUNTIFS(A2:A10,"*",B2:B10,"Paid") to count named customers with a Paid status. Every condition must be true for a row to count.

Functions used

COUNTIF Counts cells that match
COUNTA Counts non-empty cells
COUNT Counts numbers
COUNTBLANK Counts empty cells

Keep learning

Updated 2026-10-31