Table of Contents
COUNTIF is a function in Excel that allows users to count the number of cells within a given range that meet a certain criteria. When using wildcards, such as asterisks (*) or question marks (?), in conjunction with the COUNTIF function, users can specify a pattern or partial match to be counted. This feature is useful for analyzing large datasets and identifying specific data points that may have varying values but share a common pattern. By utilizing wildcards with the COUNTIF function, users can efficiently and accurately count data that meets their desired criteria, making data analysis and organization easier and more efficient.
Use COUNTIF with Wildcards in Excel
You can use the following formulas in Excel 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: COUNTIFS 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 6 cells in column A that contain “avs” in the team name.
Example 2: COUNTIFS 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 4 rows where the team contains “avs” and the position contains “gua” somewhere in the cell.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use COUNTIF with wildcards in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-countif-with-wildcards-in-excel/
stats writer. "How can I use COUNTIF with wildcards in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-countif-with-wildcards-in-excel/.
stats writer. "How can I use COUNTIF with wildcards in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-countif-with-wildcards-in-excel/.
stats writer (2024) 'How can I use COUNTIF with wildcards in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-countif-with-wildcards-in-excel/.
[1] stats writer, "How can I use COUNTIF with wildcards in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use COUNTIF with wildcards in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.