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)***** (Asterisk)****~ (Tilde) followed by ?, *, or ~**

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.

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.”

- 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.**

**Formulas are more transparent and easier to understand.****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??.”

- 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:

## 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)

- Press Enter.

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

## 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:**

- How to Count Between Two Numbers in Excel?
- Count Cells Less than a Value in Excel (COUNTIF Less)
- Count Cells that are Not Blank in Excel (Formulas)
- COUNTIF Greater Than Zero in Excel
- How to Count Unique Values in Excel (Formulas)
- Count the Number of Yes in Excel (Using COUNTIF)
- How to Remove Text after a Specific Character in Excel?