How to Count Cells With Text in Excel

To count cells with text in Excel, use =COUNTIF(range,"*"). The asterisk is a wildcard that matches any text, so the formula counts words and skips numbers, dates and empty cells. This guide compares it with COUNTA and ISTEXT, shows how to count a specific word, and explains the cells that look empty but still count.

The sample data#

Every example uses this survey column in A1:B8. It mixes words, numbers and one blank cell, which is exactly where the different counting functions disagree:

There are seven answer cells: four hold text (Yes, No, N/A, Maybe), two hold numbers (42 and 15) and one is blank. "N/A" here is typed text, not the #N/A error.

How to count cells with any text#

Use COUNTIF with a single asterisk as the criteria. COUNTIF counts the cells in a range that match a condition, and "*" means "any text, of any length":

=COUNTIF(B2:B8,"*")

1. Click an empty cell, such as D2.
2. Type =COUNTIF(B2:B8,"*").
3. Press Enter.

The result is 4. The numbers 42 and 15 are left out because wildcards only match text, and the blank cell is left out because it holds nothing. Dates are numbers in Excel, so they are skipped too, and so are the logical values TRUE and FALSE. For the basics of COUNTIF, see how to use COUNTIF or the COUNTIF function guide.

COUNTA, COUNT and COUNTIF compared#

Excel has several counting functions, and each one answers a different question. On the same column:

COUNTA is the one people often reach for by mistake. It counts anything that isn't empty, so it includes the two numbers and gives 6. Use it when you want "how many answers were given", and COUNTIF with "*" when you want "how many answers were words". The COUNTA function guide and the lesson on COUNT and COUNTA cover the differences in more depth.

COUNTA - COUNT gives the same 4 here, but it also counts errors and TRUE/FALSE values, so it is only a rough text count. COUNTIF with "*" is more precise.

How to count cells with specific text#

To count one particular word, put it in quotes as the criteria:

COUNTIF ignores capitals, so "yes" and "YES" match "Yes". The pattern "*a*" finds N/A and Maybe, because each contains an a somewhere. Partial matches are covered in COUNTIF contains text, and counting yes/no answers in how to count Yes, No and ticks.

Cells that look empty but count as text#

Two kinds of cell look blank but still count as text:

• A cell holding a space. A stray space typed by mistake is text, so "*" counts it. Select the cell and look at the formula bar, or check its length with =LEN(B4). A result of 1 or more means something is there.
• A formula that returns empty text. A formula such as =IF(A2="","",A2) shows nothing when A2 is empty, but the cell holds empty text (""). COUNTIF with "*" and ISTEXT can count these cells too.

To skip empty text, use "?*" instead. The ? wildcard means exactly one character, so "?*" means "at least one character":

=COUNTIF(B2:B8,"?*")

On the sample data it also returns 4, because none of the cells holds empty text. On a sheet full of formulas, the two counts can differ. To find cells that are truly empty versus filled, see COUNTIF not blank.

Counting text with ISTEXT and SUMPRODUCT#

ISTEXT returns TRUE when a cell holds text and FALSE otherwise. Combined with SUMPRODUCT, it gives a text count without wildcards:

=SUMPRODUCT(--ISTEXT(B2:B8))

ISTEXT checks each cell, -- turns TRUE and FALSE into 1 and 0, and SUMPRODUCT adds them up. The result is 4. This form is useful when you want to combine the text test with another condition, such as =SUMPRODUCT(ISTEXT(B2:B8)*(A2:A8<>"R7")), which returns 3.

Numbers stored as text#

A number typed with an apostrophe, like '42, or imported from another system can be stored as text. Excel then counts it as text, not as a number. The cell usually sits on the left side of the column and may show a small green triangle in the corner.

That changes your counts: COUNTIF with "*" goes up, COUNT goes down, and SUM ignores the value. If a text count seems too high, look for numbers stored as text and convert them with VALUE or the Convert to Number option on the warning icon.

When your next question is how many different values a column holds, or how many repeat, read how to count unique values and how to count duplicates.

How to count text cells that meet another condition#

To count text cells in one group only, use COUNTIFS with "*" as one of the criteria. If column C held each respondent's region, this would count the North respondents who gave a text answer:

=COUNTIFS(B2:B8,"*",C2:C8,"North")

A row counts only when both conditions are TRUE. Another common job is counting text answers for every question in a survey, one column per question. Put =COUNTIF(B$2:B$8,"*") under the first question and fill it right (Windows: Ctrl+R; Mac: ⌘R). The $ before each row number keeps rows 2 to 8 fixed while the column letter changes to C, D and so on.

Practise it#

Choosing the right counting function is a classic screening-test question. Practise COUNTIF in a live spreadsheet, including wildcards, and then compare it with COUNT and COUNTA in the COUNT and COUNTA lesson. Each lesson checks your formula instantly.