.png)
Count Cells with Specific Text in Google Sheets (The Easy Way!)
After learning how COUNTIF works in Google Sheets, you will find that there are numerous possibilities.
In this article you will learn more how to use the COUNTIF to tally how frequently a string appears in the sheet.
Are you ready?
Count Cells with a Specific Text using the COUNTIF Function
Syntax
=COUNTIF(range,”text”)
Where
text is the text you are looking for
range is where you want to look for the text
Step 1: Identify the text and range
We want to count cities of Florida in our list of cities with their corresponding states. The range is C2:C101 and the text is “Florida”.
Step 2: Select the cell, then add the formula with the format =COUNTIF(range,”text”)
The formula will now be
=countif(C2:C101,"Florida")
That’s it! You can now tally cells with a certain text using COUNTIF. Let us apply it to more cases.
Count Number of Cells that DO NOT Contain a Specific Text
Syntax
=COUNTIF(range,”<>text”)
Where
text is the text you want to exclude
range is where you want to look for the text
The <> symbol means not equal.
Step 1: Identify the text and range
We want to tally cities that are not in California. The range is C2:C101 and the text is “<>California”.
Step 2: Select the cell, then add the formula with the format =COUNTIF(range,”text”)
The formula will now be
=countif(C2:C101,"<>California")
Count number of Cells that Contain a Portion of a String
Wildcard symbols tell Google Sheets that a set of characters precede or follow a given string. We use asterisk * as it means the preceding or following characters have varying lengths.
Syntax
When you know the first part of the text
=COUNTIF(range,”string*”)
When you know the last part of the text
=COUNTIF(range,”*string”)
When you know the middle part of the text
=COUNTIF(range,”*string*”)
This will also count cases where the string appears at the start or at the end.
Step 1: Identify the text and range
We want to count cities whose states they belong to have the string “ia” in their name: at the beginning, in the middle, and at the end of their names. The range is C2:C101 and the string for each case are:
“ia*” for cells with “ia” at the start
“*ia*” for cells with “ia” at the middle
“*ia” for cells with “ia” at the end
Step 2: Select the cell, then add the formula with the format =COUNTIF(range,”text”)
See? It’s straightforward!
Count Cells with 2 or More Given Texts using the COUNTIF Function
You can determine how frequently two or more given texts appear in the selected range by making a combination of COUNTIF with ARRAYFORMULA and SUM functions.
Syntax
=ARRAYFORMULA(SUM(COUNTIF(range,{”text1”,”text2”})))
Where
text1 and text2 are the strings you are tallying
range is where you want to look for text1 and text2
Step 1: Identify the text and range
We want to count cities that are located in Texas and Florida. The range is C2:C101, and text1 and text2 are “Texas” and “Florida”, respectively.
Step 2: Select the cell, then add the formula with the format =ARRAYFORMULA(SUM(COUNTIF(range,{”text1”,”text2”})))
For our example, the formula will be
=arrayformula(sum(countif(C2:C101,{"Florida","Texas"})))
FAQs
Can the string be stored in another cell?
Yes! It’s generally straightforward to do so. The syntax is
=COUNTIF(range, cell_containing_text)
Where
cell_containing_text is the reference to the cell containing the text
range is where you want to look for the text
The difference here is that the text stored in another cell no longer needs to be enclosed in double quotes.
For counting the number of cells that do not contain a certain text, you need to use CONCATENATE function:
=COUNTIF(range,CONCATENATE(“<>”,cell_containing_text)
Can I count unique values?
Sure you can! Check out our tutorial to count unique values in Google Sheets.
