Note: The other languages of the website are Google-translated. Back to English

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

SUMPRODUCT + ISTEXT functions to count cells with text in Excel

COUNTIF function to count cells containing specific text in Excel

Easily count cells containing specific text with Kutools for Excel


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:

=COUNTIF(range, "*")

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, "*")

count cell with text 1

√ 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.

count cell with text 2

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:

=COUNTIF(range, "<>*")

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,"<>*")

count cell with text 3

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:

=COUNTIFS(range,"*?*", range, "<> ")

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, "<> ")

count cell with text 4

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:

=SUMPRODUCT(--ISTEXT(range))
or
=SUMPRODUCT(ISTEXT(range)*1)

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)

count cell with text 5

count cell with text 6

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:

=COUNTIF(range, "text value")

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 cell with text 7

count cell with text 8

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:

=COUNTIF(range, "*text value*")

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*")

count cell with text 9

count cell with text 10

√ 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.

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”.

count cell with text 11

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

2. Click Kutools > Select > Select Specific Cells.

count cell with text 12

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.

count cell with text 13

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

count cell with text 14

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.

count cell with text 12

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.

count cell with text 15

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

count cell with text 16

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



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations