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.
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.
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.
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,"<>*")
This is the result you will see when you use the COUNTIF function to count 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.
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*")
Here's the result:
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")
In our example, the count result is 0 because there are no cells that only contain the character a in them.
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.