COUNTIF Contains Text in Google Sheets (4 Ways)

In a practical scenario, you will often have huge amounts of data in your spreadsheets. In such a situation finding certain text in this huge database can be a great ordeal.

Following that train of thought, today we are going to show you how you can use COUNTIF to count cells that contain text in Google Sheets through some easy methods.

Let’s get started.

4 Ways to Use COUNTIF on Cells That Contains Text in Google Sheets

To show our processes today, we will be using the following dataset:

Each column has a various combination of data that will help us touch upon almost all situations where you might require to count the occurrences of certain text.

1. Contains Specific Text

Let’s start with something simple. From our dataset, we will be counting the number of records that contain the word Art in the Department column. We will of course be using the COUNTIF function for this, and all the processes in this article.

Step 1: In the count result cell type =COUNTIF(

Step 2: Select the range of cells you want to check. In our case, it is cell D3:D12. Press comma (,) to move to the next part.

Step 3: Type in the text condition within quotation marks (“”). Our condition is “Art”.

Note: All text in the Google Sheets formula bar should be designated within quotes.

Step 4: Close parentheses and press ENTER. Our formula:

We can also use cell reference as a condition instead of directly writing the text inside the COUNTIF function:

You don’t have to use quotation marks in this format.

Let’s move on to another column. In the Name column, you will notice that a lot of last names are Lyons. So what happens if we want to count the number of records that contain the last name Lyons?

This is one of the limitations of the COUNTIF function, it does not recognize partial text within a string. We will look at how to remedy this problem in the next section.

2. Contains Text in a String

To remedy the problem of counting partial text in a string, we will be utilizing the asterisk (*) symbol.

The asterisk is a part of a special group of symbols in Google Sheets called a wildcard. Other than the asterisk, we will be looking at one other wildcard later in this article.

I. Text at the End of a String

Now, going back to our previous problem, we are looking to count the number of records in the Name column that contains the last name, Lyons. So, we are looking to find a text at the end of the string.

We will be adding the asterisk just before the word Lyons:

With cell reference:

We have added the ampersand (&) to concatenate the two different types of values:

• “*”: Text, enclosed in quotes
• G2: Cell reference

II. Text at the Start of the String

Next, we will look at how to count text that is at the beginning of the string. This time we will be putting the asterisk (*) after our desired text.

For this example, we will be counting all the occurrences of Jack in the Name column:

With cell reference:

III. Text Anywhere in the String

For our final example of this section, we will be counting all the occurrences of the ID code 101 in the ID column.

Since the code is surrounded by other text, we will be applying asterisk (*) on either side of our desired text:

With cell reference:

3. Does Not Contain Specific Text

What we are now going to show you is the reverse of what we have been doing so far – count records that do NOT contain the given text.

For this, we will be using the not-equal-to sign (<>). This is basically the less-than and greater-than symbols of your keyboard together.

For our first example, we will count the number of records that do not contain the department Art:

We can apply the same method for partial specific text as well. Here we see all the occurrences counted in the Name column without Lyons:

Next, we will count all occurrences without the name Jack:

Finally, we will count all occurrences without the code 101 in the ID column:

Note that all of the formulas mentioned in this section can be also used with cell references.

4. Count Text with Multiple Criteria

You will be happy to know that we can also apply multiple criteria to our text count.

I. Count Multiple Text using SUM and Array

For our first example, we will see how we can count two different texts in a single column. We will count the total number of records that have the text, Jack and Lyons, in them.

1. To call our two texts, we have to utilize an array as our COUNTIF criteria: {"*Jack*","*Lyons*"}
2. Our range will obviously be the Name column: B3:B12
3. We will be doing the sum of either value thus the COUNTIF will be enclosed in the SUM function.
4. Since Google Sheets cannot directly take array values, we have to again enclose the whole formula within the ARRAYFORMULA function (you can also press CTRL+SHIFT+ENTER to add ArrayFormula).

Our final formula:

II. Using the COUNTIFS Function

The COUNTIFS function is one of the better ways to tackle multiple criteria for any type of count.

To show this off, we will be counting the number of records that have a starting ID of 123 AND is from the Art Department.

So, our formula will be:

Final Words

Counting cells that contain text with COUNTIF is not that difficult in Google Sheets. The only complexity it has is when we want to count the presence of specific text within a string. Even that is easily done.

We hope that all the methods we have discussed in this article come in handy in your day-to-day work. Please feel free to leave a comment on any queries or advice you might have.

Related Articles for Reading

• Using COUNTIF to Count Non-Blank Cells in Google Sheets
• Count Non-Empty Cells in Google Sheets (4 Easy Ways)