Table of Contents
The COUNTIF function in Google Sheets can be used with wildcards to count the number of cells in a range that match certain criteria. For example, in a range of cells, wildcards can be used to count the number of cells that contain a certain text string, such as “*book*”. This will count any cell that contains the word “book” anywhere within the cell. Wildcards can also be used to count the number of cells that match a certain pattern, such as “b?g”. This will count any cell containing a two-letter word that begins with “b” and ends with “g”.
You can use the following formulas in Google Sheets to use wildcard characters with the COUNTIF function:
Formula 1: COUNTIF with One Wildcard
=COUNTIF(A2:A11, "*string*")
This particular formula counts the number of cells in the range A2:A11 that contain “string” anywhere in the cell.
Formula 2: COUNTIF with Multiple Wildcards
=COUNTIFS(A2:A11, "*string1*", B2:B11, "*string2*")
This particular formula counts the number of cells where A2:A11 contains “string1” and where B2:B11 contains “string2.”
The following examples show how to use each formula in practice.
Example 1: COUNTIF with One Wildcard
We can use the following formula to count the number of cells in column A that contain “avs” anywhere in the cell:
=COUNTIF(A2:A11, "*avs*")
The following screenshot shows how to use this formula in practice:
From the output we can see that there are 5 cells in column A that contain “avs” in the name.
Example 2: COUNTIF with Multiple Wildcards
We can use the following formula to count the number of rows where A2:A11 contains “avs” and where B2:B11 contains “Gua”:
=COUNTIFS(A2:A11, "*avs*", B2:B11, "*Gua*")
From the output we can see that there are 2 rows where the team contains “avs” and the position contains “Gua” somewhere in the cell.
The following tutorials explain how to perform other common operations in Google Sheets: