Sometimes when working in a large spreadsheet in Google Sheets, you may need to count the number of cells that contain a specific text.
There are some pretty simple functions you can use to count the occurrence of certain words or letters inside your spreadsheet.
In this tutorial, I will show you how to count cells that contain specific text in Google Sheets.
Table of Contents
Count Cells that Contain Specific Text
The easiest way to count the number of cells that contain a specific text is by using the COUNTIF function. This will count the number of cells in a range where the text exactly matches the text you are searching for.
Here is an example of using the COUNTIF function to count the number of times the text “Matt” appears in a list of names:
The formula used in this example is:
=COUNTIF(A2:A13,“Matt”)
What this formula does is it will search in my range of A2:A13 for the text “Matt” and return the number of times this text appears. In the example above you can see that the text appears 3 times.
The only problem with this method is that this will only work if your entire cell exactly matches the text you are searching for. To count with a partial match of a cell, use the method below.
Count Cells That Contain Specific Text Anywhere In the Cells (Partial Match)
If you want to count cells that contain text anywhere in the entire cell, you will have to use wildcards in your COUNTIF so that your formula will return a count for any cell that contains the text you are searching for.
Let’s take a look at an example:
To count text where the cell is a partial match, you need to place the asterisk wildcard characters around the text you are searching for. In this example, the formula used is:
=COUNTIF(A2:A13,“*Matt*”)
The asterisk wildcard character stands for any character and any number of characters. So the COUNTIF formula in this example is searching for “Matt” with any character and any number of characters before and after the text. So it will return a count for any cell that contains that text.
Look how the first formula in this example only returns a count of 1. That is because it will not count cells where the entire cell doesn’t exactly match the text.
The second formula with the asterisks returns a count of 3 because it will return a count with a partial match.
Closing Thoughts
I have shown two different ways to count the number of cells that contain a specific text in your spreadsheet. The first way will search for exact matches, the second method will return a count of cells with a partial match.
You should learn both methods so that you know how to approach these situations in future scenarios as you continue working with spreadsheets.
More Google Sheets Tutorials:
How to Count Non-Blank Cells
How to Count the Number of Occurrences
How to Compare Two Columns
How to Count Words