Count the Number of Cells that Contain Specific Text in Excel

How to count the number of cells that contain specific text within a spreadsheet in Excel.

I'll show you how to count cells that match exactly a string or that contain part of a string.

Sections:

Count Cells that Exactly Match a String

Count Cells that Contain a Particular String Anywhere within the Cell

Notes

Count Cells that Exactly Match a String

This allows you to count all cells that have specific text in them.

You can count cells that say "red" or "green" or "out of stock" or anything you want.

To do this, we use the COUNTIF function.

Steps to Count the Cells that Match a String

1. Type =COUNTIF( in the cell where you want to see the count.
   
2. Select the range where you want to search for the text.
   
3. Type a comma to go to the next argument and then type the text that should be in the cells you want to count.
   
   Notice that the text here red is surrounded by double quotation marks.  You must put the text within the quotation marks for it to work.
4. Make sure to close the function with a closing parenthesis, or just hit enter, and you have the result:
   

In this example we counted how many cells contained the word "red" and the result was 2.

Here is the formula that was used:

Multiple Words

You can search for multiple words just like you did a single word, just change what is in between the quotation marks.

Searching for "a color" this time, we use this:

Count Cells that Contain a Particular String Anywhere within the Cell

You can also count cells that contain a part of a word, a single letter, or any specific character within the cell.  This means that you do not have to match the entire contents of the cell in order to count it.

We still use the COUNTIF function, like above, but, this time, we change how we enter the text for which to search.

Steps to Count Cells that Match Part of a String

1. Type =COUNTIF( in the cell where you want to see the count.
   
2. Select the range of cells to count.
   
3. Type a comma to go to the next argument and enter the text for which you want to search.
   
   Notice this time that there is an asterisk * before and after the text string but inside of the double quotation marks.
   This says that the text can be found anywhere in the cell and that it doesn't have to be a direct match, it just has to contain rdf, or whatever you put, somewhere in the cell.
4. Hit enter and you have the result:
   

Here is the formula that we used in this example:

You can also search for a number in the same way.

Use a Cell Reference for the Criteria

If you want to use a cell reference instead of hard-coding the criteria, like we did above, you will need to use an ampersand to connect everything.

For example, the formula in cell D1, which is =COUNTIF(A1:A8,"*rdf*") would become this:

C1 is the cell where you would put the criteria.

We have to do it like this or else we cannot keep the asterisks in there and it won't match text within other text in the cell.  It's confusing, but, once you understand it, it's not difficult.  This does not apply to the original example at the top of the tutorial that does not use an asterisk; for that, you would reference a cell like normal.

Search for Text at the Start or End of a String

This follows the same principal as the above example.

Match Start of a String: put the asterisk only at the end of the string.

Match End of a String: put the asterisk only at the start of the string.

To get more information on this, view our tutorial on counting cells that start or end with specific text.

Notes

The COUNTIF function is case-insensitive which means that it does not matter if text is upper case or lower case, this function will treat it the same.

There are many Count functions in Excel and they are very useful in a wide range of situations.  Make sure you understand how to use them so that you can create more powerful spreadsheets.

Download the spreadsheet that accompanies this tutorial so you can work with the examples we provided and test everything out.