How to Count Cells With Text in Microsoft Excel

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.

Read More

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.

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

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.

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.

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!

Original Article

Related posts