Table of Contents
COUNTIF Contains allows you to count the number of cells in a range of cells that contain a certain string of text. To use it, type in COUNTIF in a cell, followed by the range of cells you want to count, and then the text string you want to search for. The function will then return the number of cells in the range that contain the text string.
You can use the following formulas in Google Sheets to count the number of cells that contain certain strings:
Method 1: COUNTIF Contains (One Criteria)
=COUNTIF(A2:A11, "*string*")
This formula counts the number of cells in A2:A11 that contain “string” anywhere in the cell.
Method 2: COUNTIF Contains (Multiple Criteria)
=COUNTIFS(A2:A11, "*string1*", B2:B11, "*string2*")
This formula counts the number of rows where the cell in the range A2:A11 contains “string1” and the cell in the range B2:B11 contains “string2.”
Note: The * operator is a wildcard character in Google Sheets.
The following examples show how to use each method in practice.
Example 1: COUNTIF Contains (One Criteria)
We can use the following formula to count the number of cells in the Team column that contain the string “Mav”:
=COUNTIF(A2:A11,"*Mav*")
The following screenshot shows how to use this formula in practice:
From the output we can see that 5 rows in the Team column contain “Mav” somewhere in their cell.
Example 2: COUNTIF Contains (Multiple Criteria)
We can use the following formula to count the number of rows where the Team column contains “Mav” and the Position column contains “Guar”:
=COUNTIFS(A2:A11, "*Mav*", B2:B11, "*Guar*")
The following screenshot shows how to use this formula in practice:
From the output we can see that 2 rows contain “Mav” in the Team column and “Guar” in the Position column.
The following tutorials explain how to perform other common operations in Google Sheets: