The COUNTIF function counts the number of cells in a given data range that meet specific criteria or conditions.
The COUNTIF function can also return the number of cells containing text values that partially match the criterion value.
We’ll use the following dataset to demonstrate how the COUNTIF function can be used to count cells whose values partially match the criterion value:
Your dataset contains the details of 11 products.
Our dataset is small for demonstration purposes, but in practice you’ll normally be working with very large datasets.
Using wildcards in Excel
Wildcards play a very important role when we use the COUNTIF function to obtain the number of partial matches. Wildcards are special characters that can replace any character.
Only three wildcards are used in Excel:
- ? (question mark). It replaces a single character. For example, Cor ? could be Cork or Core.
- * (asterisk). Replaces one or more characters. For example, Con* could return Conman, Contrary or Contract.
- ~ ( tilde). Used to display the question mark (?) and asterisk (*). For example, Cor~ ? would return Cor ? and Con~* would return Con*.
The most commonly used wildcards in Excel are the question mark (?) and the asterisk (*).
It’s important to note that these wildcards only work with text values, not numbers.
Examples of COUNTIF use in a partial match
Example 1 – How to obtain the number of product category names containing the word phone :
Step 1 – Enter the word “phone” in cell G2. This is the value of the criterion:
Step 2 – Enter the formula =COUNTIF(C2:C11, “*”&G2&”*”) in cell G3:
Step 3 – Press Enter :
The COUNTIF function returns the value 1, meaning that there is only one partial match in the data range, which in this case is the phone category.
Explanation of the formula =COUNTIF(C2:C11, “*”&G2&”*”):
The COUNTIF function has two arguments:
- C2:C11 is the data range in which the function will search for partial matches.
- “*”&G2&”*” is the criterion or condition for the partial match. Cell G2, which contains the value of the criterion, is surrounded by asterisks (*) on either side. Note also that asterisks are enclosed in quotation marks and linked to cell G2 using the concatenation character (&).
Modifying criteria
We can change the value of the criterion in cell G2 and function COUNTIF will return the correct number of partial matches. For example, let’s change the criteria value to “Cartridge”:
The count has been updated accordingly.
COUNTIF function
The COUNTIF function we’ve used so far can only return the number of cells that satisfy a single condition. If we want to obtain the number of cells that meet more than one condition, we need to use the COUNTIFS function.
Example 2 – How to obtain the number of product category names containing the words Accessory and Product :
Step 1 – Enter the word Accessory in cell G2 and the word Product in cell H2:
Step 2 – Enter the formula =COUNTIFS(C2:C11, “*”&G2&”*”,C2:C11, “*”&H2&”*”) in cell G2:
Step 3 – Press Enter :
We get the number 1 because only the Accessory/IT product category meets both conditions.
Explanation of the formula =COUNTIFS(C2:C11, “*”&G2&”*”,C2:C11, “*”&H2&”*”) :
This formula has 2 conditions for partial matches: “*”&G2&”*” and “*”&H2&”*”.
The COUNTIFS function searches for partial matches in the same C2:C11 data range for both conditions or criteria.
Conclusion
In this tutorial, we’ve seen how to use functions COUNTIF and COUNTIFS to obtain the number of cells containing values that partially meet certain conditions.