After learning how COUNTIF works in Google Sheets, you’ll discover that there are numerous possibilities. In this article, I will show you how to use COUNTIF to count how many times a specific text appears in the spreadsheet.
Are you ready?
Counting Cells with Specific Text Using the COUNTIF Function
Syntax
=COUNTIF(range, "text")
Where:
- “text” is the text you are searching for.
- range is the location where you want to search for the text.
Step 1: Identify the Text and Range
We want to count the cities in Florida from our list of cities with their corresponding states. The range is C2:C101, and the text is “Florida”.
Step 2: Select the Cell and Add the Formula with the Format =COUNTIF(range, "text")
The formula will be:
=COUNTIF(C2:C101, "Florida")
And voilà! You can now count cells with specific text using COUNTIF. Let’s apply it to other cases.
Counting the Number of Cells that Do Not Contain Specific Text
Syntax
=COUNTIF(range, "<>text")
Where:
- “text” is the text you want to exclude.
- range is the location where you want to search for the text.
The “<>” symbol means “not equal to”.
Step 1: Identify the Text and Range
We want to count the cities that are not in California. The range is C2:C101, and the text is “<>California”.
Step 2: Select the Cell and Add the Formula with the Format =COUNTIF(range, "<>text")
The formula will be:
=COUNTIF(C2:C101, "<>California")
Counting the Number of Cells Containing a Part of a Text
The wildcard symbols tell Google Sheets that a set of characters precedes or follows a given text. We use the asterisk (*) as it signifies that the preceding or following characters have a variable length.
Syntax
When you know the first part of the text:
=COUNTIF(range, "text*")
When you know the last part of the text:
=COUNTIF(range, "*text")
When you know the middle part of the text:
=COUNTIF(range, "*text*")
This will also count cases where the text appears at the beginning or end.
Step 1: Identify the Text and Range
We want to count the cities whose states have the text “ia” in their names: at the beginning, in the middle, and at the end of their names. The range is C2:C101, and the texts for each case are:
- “ia*” for cells with “ia” at the beginning.
- “*ia” for cells with “ia” at the end.
Step 2: Select the Cell and Add the Formula with the Format =COUNTIF(range, "text")
You see? It’s simple and straightforward!
Counting Cells with Two or More Texts Using the COUNTIF Function
You can determine how frequently two or more texts appear in the selected range by combining COUNTIF with ARRAYFORMULA and SUM.
Syntax
=ARRAYFORMULA(SUM(COUNTIF(range,{"text1","text2"})))
Where:
- text1 and text2 are the texts you want to count.
- range is the location where you want to search for text1 and text2.
Step 1: Identify the Text and Range
We want to count the cities in Texas and Florida. The range is C2:C101, and text1 and text2 are “Texas” and “Florida” respectively.
Step 2: Select the Cell and 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 text be stored in another cell?
Yes! It’s usually straightforward to do. The syntax is:
=COUNTIF(range, cell_containing_text)
Where:
- cell_containing_text is the reference to the cell containing the text.
- range is the location where you want to search for the text.
The difference here is that the text stored in another cell no longer needs to be enclosed in quotation marks.
To count the number of cells that do not contain a certain text, you need to use the CONCATENATE function:
=COUNTIF(range,CONCATENATE("<>",cell_containing_text))
Can I count unique values?
Of course, you can! Check out our tutorial on counting unique values in Google Sheets.
If you enjoyed this article, you might also like our articles on how to count checkboxes in Google Sheets or how to sort in Google Sheets.
If you want to learn how to attach Google Sheets to an email, we suggest checking out our detailed guide.
Use our invoice due date tracker to easily keep track of the due date of your invoices from your spreadsheet with just a few clicks.
For more information on Google Sheets and other marketing tips, visit Crawlan.com.
