ExcelInsider Team
We often face situations where we need to count how many times a specific word appears within text entries rather than matching the text exactly. For example, a sales manager may want to count all products that include the word “phone” in their name, even if the full text is “Mobile Phone” or “Telephone Set.” This is where the COUNTIF function works well. This allows for flexible keyword based counting in datasets.
To use COUNTIF partial match function, follow these steps:
➤ Prepare your Excel sheet with a column containing text entries such as product names or task descriptions.
➤ Type the keyword you want to search for in another cell (e.g., “delay”).
➤ Use this formula: =COUNTIF(range, “*”&delay&”*”)
➤ This counts all cells that contain the keyword anywhere in the text.
In this article, we will learn how to count partial matching text of different types using the COUNTIF function in Excel.
How Does COUNTIF Function Work with Partial Match?
COUNTIF is a function in Excel that lets us count cells containing a certain keyword even if the keyword is part of a longer sentence. We do this through using wildcards like the asterisk *, which matches any number of characters before or after the keyword.
For example,
=COUNTIF(A2:A10, "*phone*")
This counts all cells in the range A2:A10 that contain the word “phone,” such as “Cordless Phone” or “Smartphone Accessories”.
Use COUNTIF Function to Count Partial Match in Excel
The COUNTIF partial match function allows us to count how many cells contain a specific word or part of a word, even if it’s not an exact match. This is good when we analyze text data such as product names, customer feedback, or order statuses.
We have a dataset that contains information of a customer service team. Here, we will track the number of complaints that mention the word “delay” in the complaint description column.
Steps:
➤ Open your Excel worksheet. We have taken a table that contains Ticket ID in Column A, Complaint Description in Column B, and Department in Column C.
➤ Click on cell E2 and type the word you want to search for. For example, we want to search for “delay” that is contained in Column B (Complaint Description).
➤ Click on cell E3 and type the formula:
=COUNTIF(B2:B11,"*"&E2&"*")
➥ B2:B11 is the range of cells being checked in Complain Description.
➥ E2 is the cell containing the partial keyword (e.g., "Delay").
➥ "*" is a wildcard that matches any text.
➥ "*"&E2&"*" allows for partial matches anywhere in the text (beginning, middle, or end).
➤ Press Enter and view the result in E3. The result will count all cells that include the keyword “Delay” and show the number.
Note:
➥ The asterisk “*” is a wildcard that matches any number of characters before or after the keyword.
➥ The “&” symbol is used to combine the keyword in E2 with the wildcards.
➥ COUNTIF is not case-sensitive, so “Delay” and “delay” are treated the same.
➥ This method works for partial words, full words, or any substring.
Count Cells Containing Text Ending with a Particular Substring
We use the COUNTIF function with a wildcard to count the number of cells when the text ends with a particular substring. We use this when we want to identify values that follow a naming pattern, such as product names ending in a keyword like “Phone”.
We have an Excel worksheet that contains data of an online auction company. Here, we will track various electronics items on sale. We will know how many items are phones. We will identify and count only those descriptions that end in “Phone” using a COUNTIF partial match function.
Steps:
➤ Open your Excel dataset. We have taken a table that contains Item ID in Column A, Item Description in Column B, and Starting Price in Column C.
➤ Click on cell E2 and type “phone”. This is the partial text we want to match at the end of each description.
➤ Click on cell E3 and enter the following formula:
=COUNTIF(B2:B11,"*"&E2&"*")
➥ B2:B11 is the range where we are searching.
➥ "*"&E2 creates a pattern that means "ends with the value in E2".
➤ Press Enter to view the result. Excel will return the number of cells in B2:B11 that end with the word “phone”.
Use COUNTIF Function to Count Cells With a Particular Pattern
We can use the COUNTIF function to count how many cells contain a specific word or part of a word. This method is good when we want to identify specific character patterns, such as any string containing the letter X followed by any two characters.
We have taken a dataset that contains information of a warehouse inventory. We want to count how many product codes contain a specific pattern: the letter “X” followed by any two characters.
Steps:
➤ We have a dataset that contains Product ID in Column A, Product Code in Column B, and Brand Name in Column C.
➤ Click on cell E2 and type *X??.
➥ * represents any number of characters.
➥ X is the literal character to match.
➥ ?? represents exactly two characters after X.
➤ Click on cell E3 and type this formula:
=COUNTIF(B2:B11,"*"&E2&"*")
➤ Press Enter, Excel will display the count of product codes matching the pattern. In our example, there are 10 product codes with an “X” followed by any two characters.
Note:
➥* (asterisk) matches any sequence of characters.
➥? (question mark) matches exactly one character.
➥You can adjust the number of ? to control the exact character length after “X”. This method works for text only; it will not match numeric only values unless formatted as text.
COUNTIF Function to Count Cells Containing Wildcard Characters
We use the COUNTIF function with a wildcard character to count how many cells contain literal wildcard symbols like asterisks * or question marks ?. Since these symbols normally act as wildcards in Excel, we use the tilde ~ character to treat them as regular characters. This method is good for IT departments, developers, or teams auditing text entries that include programming symbols, password formats, or code inputs.
We have an Excel worksheet that contains data from an organization’s internal IT audit. We will identify how many employee passwords include an asterisk * using the COUNTIF partial match method with an escaped wildcard by writing ~* in the formula.
Steps:
➤ Open your worksheet that contains your data. We have a dataset that contains Employe ID in Column A, Password in Column B, and Department in Column C.
➤ Click on cell E2 and type *~*.
➥ ~* escapes the asterisk so Excel looks for the literal * character.
➤ Click on cell E3 and enter the following formula:
=COUNTIF(B2:B11,"*"&E2&"*")
➤ Press Enter to display the number of entries in the password list that contain the asterisk (*). In our example, there are 6 such passwords.
Using COUNTIFS to Count Cells With Partial Matches
This method uses the COUNTIFS function with wildcard characters to apply multiple partial match conditions across different columns. This method is best when we want to filter or summarize data where both conditions need to be partially matched such as in product inventories, customer records, or sales reports..
We have an Excel worksheet that contains a product inventory list. Here, we want to find out how many items fall under the “Telephone” category and are manufactured by Canon World using this method.
Steps:
➤ Open your Excel that contains your data. We have a dataset that contains Product ID in Column A, Product Category in Column B, and Manufacturers in Column C.
➤ Click on cell E2 and type the partial text you want to search in the Product Category column for example, type telephone. Next, click on cell F2 and type the partial manufacturer name for example, Canon.
➤ Click on cell E3 and type the following formula:
=COUNTIFS(B2:B11,"*"&E2&"*",C2:C11,"*"&F2&"*")
➥ C2:C11 is the Manufacturer column.
➥ "*"&E2&"*" matches any cell containing the value in E2, regardless of what’s before or after it.
➥ "*"&F2&"*" does the same for manufacturer names.
➤ Press Enter to display the result. Excel will display the number of rows that match both partial criteria. In our example, the result is 3.
Note:
➥ COUNTIFS supports multiple criteria ranges and matching conditions.
➥ Use * to perform partial matches, useful when full text isn’t consistent or predictable.
COUNTIFS is not case sensitive.
➥ This method is great for filtering lists by multiple attributes (e.g., department + role, product + region, etc.)
Frequently Asked Questions
Can COUNTIF handle case-sensitive matches?
No, COUNTIF is not case sensitive. It treats “Phone” and “phone” as the same.
Can I search for multiple keywords?
Not directly with one COUNTIF. Use helper columns or combine multiple COUNTIF functions using +.
Why is my COUNTIF returning 0 even when the word is present?
Check that you are using wildcards correctly and that there are no extra spaces or formatting issues in the text.
What’s the difference between COUNTIF and COUNTIFS in a partial match?
COUNTIF handles one condition. COUNTIFS can handle multiple conditions, each with its own partial match using wildcards.
Concluding Words
The COUNTIF function to find partial matches is a quick way to count values based on keyword presence. This is good when we work with unstructured or descriptive data in Excel. Also using wildcards, you can do even some smarter, more flexible data analysis without complex formulas.
