Microsoft Excel logo on a green background

Do you want to count the number of cells that contain any or specific text while ignoring all other cells? If so, Microsoft Excel has a dedicated function to help you do that. We’ll show you how to use it.

In Excel, you can use the COUNTIF function to count either cells containing any text or cells containing specific text. Use the method below that works for your specific situation.

RELATED: How to Count Cells in Microsoft Excel

Count Cells With Any Text in Excel

To count the number of cells that contain any text, but ignore any numbers, blank cells, and errors, use the method here.

First, open your spreadsheet with Microsoft Excel. In the spreadsheet, select the cell in which you want to display the result.

Select a cell in an Excel spreadsheet.

In the selected cell, type the following COUNTIF function and press Enter. Make sure to replace D2 and D6 in this function with the range where your cells to be counted are.

=COUNTIF(D2:D6,"*")
Advertisement

Here, the * (asterisk) argument tells the function to only count cells containing text.

Enter the COUNTIF function to count all text cells.

You can also count the cells that contain anything but text. To do so, use the following modified version of the COUNTIF function. In this function, the argument specifies that only the non-text cells should be counted.

If a cell has a mix of both text and numbers, it won’t be counted.

=COUNTIF(D2:D6,"<>*")

Enter the COUNTIF function to count all non-text cells.

This is the result you will see when you use the COUNTIF function to count the number of cells containing any text.

The number of cells containing any text.

RELATED: How to Highlight Blanks or Errors in Microsoft Excel

Count Cells With Specific Text in Excel

To make Excel only count the cells that contain specific text, use an argument with the COUNTIF function.

First, in your spreadsheet, select the cell in which you want to display the result.

Select a cell in an Excel spreadsheet.

Advertisement

In the selected cell, type the following COUNTIF function and press Enter. In the function, replace D2 and D6 with the range where your cells are. Also, replace a with any character or word that your cell should have for it to be counted.

The below function counts all cells that contain the letter a. This means, in our example, it will count both Mahesh and David as both these names have the letter a in them.

=COUNTIF(D2:D6,"*a*")

Enter the COUNTIF function to count cells containing specific text.

Here’s the result:

The number of cells containing specific text.

To make the function count those cells that only have your specified character or word in them, remove the * (asterisk) sign from before and after your character or word, as follows.

=COUNTIF(D2:D6,"a")

Enter the COUNTIF function to count cells only containing specific text.

In our example, the count result is 0 because there are no cells that only contain the character a in them.

The number of cells containing only specific text.

And that’s how you specify what cells to consider for calculation in your Microsoft Excel spreadsheets. Very useful!


Did you know Excel can also count blank or empty cells in your spreadsheets? Check that out if you’re interested.

RELATED: How to Count Blank or Empty Cells in Microsoft Excel

Profile Photo for Mahesh Makvana Mahesh Makvana
Mahesh Makvana is a freelance tech writer who specializes in writing how-to guides. He has been writing tech tutorials for over a decade now. He’s written for some of the prominent tech sites including MakeUseOf, MakeTechEasier, and Online Tech Tips.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.