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:

primary worksheet for countif contains text in google sheets

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(

starting COUNTIF function for countif contains text in google sheets

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.

adding range to COUNTIF

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

finalizing the COUNTIF function

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

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

=COUNTIF(D3:D12, "Art")

result of countif contains text in google sheets

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

=COUNTIF(D3:D12, G2)

using cell reference for countif contains text in google sheets

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?

limitation of using direct text in COUNTIF

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:

=COUNTIF(B3:B12,"*Lyons")

using the asterisk symbol in COUNTIF text

With cell reference:

=COUNTIF(B3:B12,"*”&G2)

adding ampersand to cell reference in COUNTIF

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:

=COUNTIF(B3:B12, "Jack*")

counting first name Jack for countif contains text in google sheets

With cell reference:

=COUNTIF(B3:B12, G2&"*")

using cell reference in COUNTIF

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:

=COUNTIF(C3:C12, "*101*")

modifying COUNTIF formula to count text anywhere in the cell in Google Sheets

With cell reference:

=COUNTIF(C3:C12, "*"&G2&"*")

using cell reference to count text anywhere in a cell

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:

=COUNTIF(D3:D12,"<>Art")

COUNTIF cells that do not contain text in Google Sheets

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:

=COUNTIF(B3:B12,"<>*Lyons")

COUNTIF cells that do not contain partial text last in a cell

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

=COUNTIF(B3:B12,"<>Jack*")

COUNTIF cells that do not contain partial text first in a cell

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

=COUNTIF(C3:C12,"<>*101*")

COUNTIF cells that do not contain partial text anywhere in a cell

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:

=ARRAYFORMULA(SUM(COUNTIF(B3:B12,{"*Jack*","*Lyons*"})))

COUNTIF with multiple criteria with ARRAYFORMULA

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:

=COUNTIFS(C3:C12,"*123*",D3:D12,"Art")

COUNTIF for multiple criteria with COUNTIFS

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

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo