How to count cells with text in Excel

Excel is everywhere. As a helpful and powerful tool for data analysis and documentation, we often use it in work and life. In some cases, we may need to better understand our data to perform data analysis. In this tutorial, we will demonstrate multiple ways to count cells with text in Excel.

COUNTIF/COUNTIFS function + Wildcard character to count cells with text in Excel

• COUNTIF function to count cells with text
• COUNTIF function count cells with no text
• COUNTIFS function to count cells with text excluding visually blank cells

SUMPRODUCT + ISTEXT functions to count cells with text in Excel

COUNTIF function to count cells containing specific text in Excel

• Count cells containing certain text with an exact match
• Count cells containing certain text with a partial match

Easily count cells containing specific text with Kutools for Excel

• Easily count cells containing specific text with Kutools for Excel
• Count and select cells that partially match specific text with Kutools

---

COUNTIF/COUNTIFS function + Wildcard character to count cells with text in Excel

COUNTIF function to count cells with any text

To count cells containing any text, we use the asterisk symbol (*) in the COUNTIF formula. The syntax of the COUNTIF function is as follows:

Because the asterisk (*) is a wildcard that matches any sequence of characters, the formula counts all cells that contain any text.

For example, to count cells with text in the range A2:A15 as shown below, type the below formula into a blank cell, and press the Enter button to get the result.

=COUNTIF(A2:A15, "*")

√ Notes:

• What is not counted as text:

1. Cells with any text;
2. Special characters;
3. Numbers formatted as text;
4. Visually blank cells that contain an empty string (=""), apostrophe (‘), space.

• What is not counted as text:

1. Numbers;
2. Dates;
3. Logical values of True and False;
4. Errors;
5. Blank cells.

In this case, we can clearly understand why there are 8 cells with text in the example worksheet.

COUNTIF function count cells with no text

To count cells that do not contain text in a data range, use the not equal to the logical operator (<>) and asterisk symbol (*) in the COUNTIF formula. The syntax is as follows:

Because the not equal to the logical operator (<>) and asterisk symbol (*) means not equal to any characters, the formula counts the cells without any text.

For example, to count cells with no text in the range A2:A15, type the below formula into a blank cell, and press the Enter button to get the result.

=COUNTIF(A2:A15,"<>*")

Then you get the number of cells that contain non-text values.

COUNTIFS function to count cells with text excluding visually blank cells

To count cells with text that excludes visually blank cells, use the asterisk symbol (*), question mark (?), and the not equal to (<>) logical operator in the formula. The syntax is as follows:

A question mark symbol (?) matches any single character, and an asterisk symbol (*) matches any sequence of characters. A question mark surrounded by asterisks (*?*) means that at least one character should be in the cell, so the empty string and apostrophe won’t be counted.

The not equal symbol (<>) plus one empty space means not to count the cells with the space character.

For example, to count cells with text without visually blank cells in the range A2:A15, type the below formula into a blank cell, and press the Enter button to get the result.

=COUNTIFS(A2:A15,"*?*", A2:A15, "<> ")

Then you can get the number of cells that only contain visible text.

√ Notes:

1. The COUNTIFS function supports multiple conditions. In the example above, to count cells with text, and also exclude cells that contain only one space, empty string, and apostrophe, which all make the cells look blank.
2. If you want to count cells with text, and not to count cells that contain only one space, the syntax becomes = COUNTIFS(range,"*", range, "<> ").

SUMPRODUCT + ISTEXT functions to count cells with text in Excel

The second way to count cells with text values is to use the SUMPRODUCT function together with the ISTEXT function. The syntax is as follows:

or

The ISTEXT function returns TRUE or FALSE when a cell contains text or non-text. And the double negative (--) in the first syntax and the multiplication operation in the second syntax are two ways to coerce TRUE and FALSE into the numeric 1 and 0.

Then SUMPRODUCT function returns the sum of all the ones and zeros in the search range and gives a final count.

In this case, to count cells with text in the range A2:A15, type the below formula into a blank cell, and press the Enter button to get the result.

=SUMPRODUCT(--ISTEXT(A2:A15))

or

=SUMPRODUCT(ISTEXT(A2:A15)*1)

No matter which syntax you use, the returned result will be 8.

COUNTIF function to count cells containing specific text in Excel

Count cells containing certain text with an exact match

To perform an exact match of the COUNTIF function, just enter the full text with quotation marks in the second argument in the formula. Here is the syntax:

To add text in a formula in Excel, enclosing the text with quotation marks (“…”) is necessary.

For example, you want to find how many cells in the range A2:A10 contain exactly the word “pen” or “pencil”, type the below formula into a blank cell, and press the Enter button to get the result.

=COUNTIF(A2:A10, "pen")

or

=COUNTIF(A2:A10, "pencil")

Count cells containing certain text with a partial match

To count cells with a partial match, place the text between two asterisks (*) and enclose them with quotation marks (“”). Then it allows COUNTIF to count all the cells containing the text and anything before and after it. Here is the syntax:

In this case, suppose you want to count how many cells contain “pen” or “pencil” as part of their contents in any position, type the below formula into a blank cell, and press the Enter button to get the result.

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

or

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

√ Notes: COUNTIF is not case-sensitive.

Easily count cells containing specific text with Kutools for Excel

Besides using a formula to count the cells with text, you can use the Select Specific Cells utility of Kutools for Excel to quickly count and select the cells with specific text in Excel.

No matter you want to count cells containing certain text with an exact match or with a partial match, our Kutools for Excel can help you reach your goal easily.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Go to Download Free Trial 30 daysPurchase PayPal / MyCommerce

After free installing Kutools for Excel, let's have a look at how it works!

Count and select cells that exactly match specific text with Kutools

In this case, you want to find how many cells in the range A2:A10 contain exactly the text “pen”.

1. Select the range you want to count the cells containing specific text from.

2. Click Kutools > Select > Select Specific Cells.

3. In the Select Specific Cells dialog box, you need to:

• Select Cell option in the Selection type section;
• In the Specific type section, select Equals in the drop-down list, enter pen in the text box;
• Click the OK button. Then a prompt box pops up to tell you how many cells match the condition.

• Click the OK button in the prompt box and all qualified cells are selected in Excel at the same time.

Count and select cells that partially match specific text with Kutools

Suppose you want to count how many cells in the range A2:A10 contain the word "pen" as part of the contents in any position. The trick is similar to the last one.

1. Select the range you want to count the number of cells containing specific text.

2. Click Kutools > Select > Select Specific Cells.

3. In the Select Specific Cells dialog box, you need to:

• Select Cell option in the Selection type section;
• In the Specific type section, select Contains in the drop-down list, enter pen in the text box;
• Click the OK button. Then a prompt box pops up to tell you how many cells match the condition.

• Click the OK button in the prompt box and all qualified cells are selected in Excel at the same time.

Click to download Kutools for Excel for a 30-day free trial.