How to COUNTIF Partial Match in Excel?

The COUNTIF function in Excel counts the number of cells within a range that meet the given condition.

When we use COUNTIF with partial matching, it counts the number of cells containing a specific text string regardless of its position in the cell.

One scenario where this could be useful is when you have addresses in a column, and you want to find out all the addresses that have a specific city name in them.

What makes partial matching possible is wildcard characters. Therefore we must use wildcard characters in the criteria argument.

This tutorial gives four examples of how to COUNTIF partial match in Excel and one example of how to COUNTIFS partial match in Excel.

Table of Contents

What Is a Partial Match in Excel?

In Excel, partial match refers to searching for a specific text string within a larger text string.

A partial match occurs when the text searched for appears as a substring within the larger text string rather than as an exact match.

One scenario where this could be useful is when you have addresses in a column, and you want to find out all the addresses that have a specific city name in them.

Partial matching can be helpful for quickly finding and analyzing data in a large dataset, especially when the exact spelling or formatting of the data may be inconsistent.

Using Wildcard Characters in COUNTIF for Partial Match in Excel

Wildcard characters are special symbols used in search queries to represent one or more characters or even an entire text string.

Wildcard characters are indispensable when using the COUNTIF function to return the count of cells containing partial matches.

Here are the three wildcard characters used in Excel:

? (Question Mark) Represents any single character. For example, the search term “c?p” will match “cap”, “cup”, “cop”, and so on.
* (Asterisk) Represents any number of characters, including none. For example, the search term “*west” will match “Northwest”, “Southwest”, and so on.
~ (Tilde) followed by ?, *, or ~ The tilde is used as an escape character to indicate that the following character should be treated as a literal character and not as a wildcard character. We use the tilde to find a question mark, asterisk, or tilde. For example, the search term “bl07~?” matches “bl07?”, “bl07~*” matches “bl07*”, and “bl07~~” matches “bl07~”.

Let’s look at the following examples of COUNTIF partial match in Excel.

Example #1: How to Use COUNTIF Partial Match to Count Cells Containing a Specific Substring

We use the asterisk (*) wildcard in the criteria argument of the COUNTIF function to return the count of cells containing a particular substring anywhere in the cell.

Let’s consider the following dataset showing a sample of inkjet and toner cartridges sold by a particular online store.

dataset to COUNTIF Partial Match in Excel

We want to use COUNTIF partial match to determine the number of toner cartridges sold by the online store.

We use the following steps:

Select cell E2 and type in the text “toner.”

Enter the partial text you want to count in cell E2

Select cell E3 and type in the following formula:

=COUNTIF(B2:B7,"*"&E2&"*")

Press Enter.

The count of cells containing the substring “toner” is displayed in cell E3.

Note that the COUNTIF function is not case-sensitive so it will count partial matches regardless of the case of the letters.

Explanation of the formula

=COUNTIF(B2:B7,”*”&E2&”*”)

The COUNTIF function has the following syntax:

=COUNTIF(range, criteria)

In the easiest form, the COUNTIF function says:

=COUNTIF(Where do you want to search, What do you want to search).

In our example, the COUNTIF function searches the cell range B2:B7 for cells containing the substring “toner.”

(“*”&E2&”*”) We use the ampersand (&) character to surround the substring in cell E2 with asterisk wildcard characters.

The asterisk preceding the substring represents any number of characters before the substring, and the asterisk after the substring stands for any number of characters after the substring.

Advantages of Inputting Criteria in Cells Rather Than Hard-Coding Them in Formulas

We input the substring “toner” in a cell rather than hard-coding it directly in the formula because of the following advantages of inputting criteria in cells:

Ease of modification and greater flexibility. By inputting the criteria in cells, we can easily change them without modifying the formula itself. For example, suppose we wanted to determine the number of inkjet cartridges in our example dataset. In that case, all we will need to do is change the criteria in cell E2 to “inkjet,” and the formula in cell E3 will recalculate accordingly, as seen below:

Using cell reference in partial match count Excel

Formulas are more transparent and easier to understand. When others look at the worksheet, they can easily see the criteria we used to generate the results.
Ease of troubleshooting errors in formulas. If we have hard-coded values, it can be difficult to pinpoint where the error is occurring. But when we input criteria in a separate cell, we can quickly check to ensure the criteria are correct.

Also read: Find the Closest Match in Excel (Nearest Value)

Example #2: How to Use COUNTIF Partial Match to Count Cells Containing Text Ending With a Particular Substring

We use the asterisk (*) wildcard in the criteria argument of the COUNTIF function to return the count of cells containing text strings that end with a particular substring.

Suppose we have the following list of assorted items being auctioned by a particular auctioneer.

We want to use COUNTIF partial match to establish the number of phones on auction.

We use the below steps:

Select cell E2 and type in the substring “phone.”

Select cell E3 and type in the below formula:

=COUNTIF(B2:B8,"*"&E2)

Press Enter.

The number of phones on auction is shown in cell E3.

If we want to find out the number of other items on sale, for example, tablets, we need to change the criteria in cell E2 accordingly.

Also read: How to Compare Two Cells in Excel? (Exact/Partial Match)

Example #3: How to Use COUNTIF Partial Match to Count Cells Containing a Substring With a Particular Pattern

We use the asterisk (*) and question mark (?) wildcards in the criteria argument of the COUNTIF function to return the count of cells containing a substring with a particular pattern.

Suppose we have a dataset of mobile phones with their product codes in column A.

We want to use the COUNTIF partial match to ascertain the number of product codes containing the letter X followed by any two characters, regardless of what those characters are.

Here’s how we do it:

Select cell E2 and type in the substring “*X??.”

enter the partial match substring pattern

Select cell E3 and type in the formula below:

=COUNTIF(A2:A6,E2)

Press Enter.

The number of cells containing product codes that have a letter X followed by any two characters is shown in cell E3:

Also read: Excel If Statement Multiple Conditions Range

Example #4: How to Use COUNTIF Partial Match to Count Cells Containing Wildcard Characters

We use the tilde (~) followed by a particular wildcard character in the criteria argument of the COUNTIF function to return the count of cells containing wildcard characters.

Let’s consider the following dataset of user passwords in a particular firm.

We want to use the COUNTIF partial match to count the number of user passwords containing the asterisk (*) wildcard character.

We use the steps below:

Select cell E2 and type in the criteria *~** (asterisk followed by a tilde and two asterisks).

Select cell E3 and type in the below formula:

=COUNTIF(B2:B7,E3)

COUNTIF formula to find the partial match

Press Enter.

The number of cells containing passwords with the asterisk wildcard character is shown in cell E3.

Result to count partial match using countif

Also read: Check If Value is in List in Excel

Example #5 – Using COUNTIFS to Count Cells With Partial Matches

We can apply only one criterion to the range when we use the COUNTIF function to count cells containing partial matches.

However, we can apply multiple criteria when we use the COUNTIFS function.

We give an example of how to COUNTIFS partial match in Excel.

We have the following dataset showing products, their categories, and manufacturers.

We want to use the COUNTIFS function to calculate the number of telephone categories manufactured by Canon World.

We use the following steps:

Select cell F2, type in “telephone,” select cell F2 and enter “canon.”

Select cell E2 and type in the following formula:

=COUNTIFS(B2:B10,"*"&F2&"*",C2:C10,"*"&G2&"*")

Press Enter.

The number of cells that meet the conditions specified in the formula is displayed in cellF3.

This tutorial showed four examples of how to COUNTIF partial match in Excel and one example of how to COUNTIFS partial match in Excel.

We hope you found the tutorial helpful.

Other Excel articles you may also like: