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?
=COUNTIF(range,”text”)
Where
text is the text you are looking for
range is where you want to look for the text
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”.
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.
=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.
We want to tally cities that are not in California. The range is C2:C101 and the text is “<>California”.
The formula will now be
=countif(C2:C101,"<>California")
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.
=COUNTIF(range,”string*”)
=COUNTIF(range,”*string”)
=COUNTIF(range,”*string*”)
This will also count cases where the string appears at the start or at the end.
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
See? It’s straightforward!
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.
=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
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.
For our example, the formula will be
=arrayformula(sum(countif(C2:C101,{"Florida","Texas"})))
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)
Sure you can! Check out our tutorial to count unique values in Google Sheets.
If you enjoyed this article, you might also like our article on how to count checkboxes in Google Sheets or our article on how to sort on Google Sheets.
If you want to learn how to attach Google Sheets to email, we also suggest checking out our detailed guide.
Use our bill due date tracker to easily track your bill due date from your spreadsheet in just a few clicks.
Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.
.png)
