When you’re working with large datasets in Google Sheets, you will sometimes need to count the cells that contain a specific text.
This could be names or ids or even dates.
And thanks to awesome formulas in Google Sheets, this can be done in seconds.
In this tutorial, I will show you a couple of scenarios where you can count cells that contain specific text.
Count Cells That Contain a Specific Text
Suppose you have a dataset as shown below and you want to quickly count the number of times the name ‘Mark’ occurs in column A.
Below is the formula you can use to do this:
COUNTIF(A2:A13,"Mark")
Here is how this above COUNTIF formula works:
- The first argument of this formula is the range where you have the data, In this example, it’s A2:A13 which has the names
- The second argument is the criteria. This is what is used to check the value in the cell and if this criterion is met, then the cell is counted. In this example, I have manually entered the name Mark in double-quotes. If you have this criterion in a cell, you can also use the cell reference here.
The above formula does a conditional count by going through all the cells in the range and counting those cells where the criterion text is present.
In this example, I have used a name, but it could be any text – such as ids or numbers.
Count Cells That Do Not Contain a Specific Text
You can modify the COUNTIF function slightly to give you the count of cells that do not contain a specific text.
For example, if I have the dataset as shown below and I want to count all the cells where the name is not Mark, I can easily do this with the COUNTIF function.
Below is the formula that will give the count of all the cells where the name is not Mark:
=COUNTIF(A2:A13,"<>Mark")
The above formula uses the range of the cells as the first argument and the criteria used is “<>Mark”. Here the not-equal-to sign needs to be within the double quotes as well.
Count Cells That Contain Text (Anywhere in the Cell/Partial Match)
In the examples above, we checked for the entire cell content.
So if we wanted to count all the cells that contained the name Mark, we used “Mark” as the criterion. This counted all the cells where the entire cell content was ‘Mark’.
But let’s say, you have a dataset of full names (or some other text) along with the name and you want to count all the cells that contain the name ‘Mark’, then you can use the above formula.
In this case, you need to use the COUNTIF function with wild card characters.
Below is the formula that will give you the count of all the cells that have the word ‘Mark’ in it.
=COUNTIF(A2:A13,"*mark*")
In the above function, the criterion is flanked by an asterisk symbol (*) on both sides.
As the asterisk sign (*) is a wild card character that can represent any number of characters in a formula. This means that where this formula checks for the given condition, there could any number of characters/words before and after the criteria.
In simple terms, if the word Mark (or whatever your criterion is) is present in the cell, this formula would count the cell.
In case you want to count cells by checking for multiple conditions, you need to use the COUNTIFS function.
I hope you find this tutorial useful and it helps you count cells with specific text in Google Sheets.
You may also like the following Google Sheets tutorials:
