The** COUNTIF **function is one of the most frequently used functions in Excel. A lot of tasks can be done using this function, one of them is the partial match. Today I am going to show you how to use the COUNTIF function for a partial match in Excel. For this session, I am using Office 365, feel free to use yours.

## How to Use COUNTIF Function for Partial Match in Excel: 3 Ideal Examples

Here, I have the following dataset. It contains several **Book Name**Â along with their respective details. I will perform the partial match by using the **COUNTIF** function in Excel.

Note that this is a basic table with simple data. In a practical scenario, you may counter a much larger and more complex dataset.

### 1. Use the COUNTIF Function for Partial Match in Excel

As the name suggests the **COUNTIF **functions count items that match the provided criteria. I have taken the following dataset for this example. Here, I will set the criteria and count when the match is found. For example, I have set the criteria value as a **Horror** genre. Now, I will use the **COUNTIF** function to find a partial match for this example. Since the agenda is to perform a partial match, I will use the “Asterisk sign (*)” in the criteria field as a wild card.

**Steps:**

- Firstly, select the cell where you want the
**No. of Books**. Here, I selected cell**Â C16**. - Secondly, in cell
**C16**write the following formula.

`=COUNTIF(D5:D12,"*"&C15&"*")`

- Thirdly, press
**Enter**to get the result.

**COUNTIF**function, I selected the cell range

**D5:D12**as the

**range**and

**“*”&C15&”*”**as the criteria. The formula counts the number of cells from cell range

**D5:D12**that match partially with the value in cell

**Â C15**.

### 2. Employ COUNTIF Family Function for Partial Match in Excel

Similar to the **COUNTIF** function you can use its family function to perform the counting over a partial match. And the function is called **COUNTIFS**. The mechanism of the **COUNTIFS **function is similar to **COUNTIF**, the only change is that this function allows multiple ranges and criteria to check. Here, I will show you 2 different examples of using the **COUNTIF** family function for the partial match in Excel.

#### 2.1. Apply COUNTIFS for Partial Match

This example is similar to the previous one. The only change is I will use a different formula to achieve the same result. Letâ€™s see how you can do that.

**Steps:**

- In the beginning, select the cell where you want the
**No. of Books**. - Then, write the following formula in that selected cell.

`=COUNTIFS(D5:D12,"*"&C15&"*")`

- In the end, press
**Enter**to get the**No. of Books**.

**COUNTIFS**function, I selected cell range

**D5:D12**as criteria_range1 and

**“*”&C15&”*”**as criteria1. The formula will return the number of cells in cell range

**D5:D12**that contains the value in cell

**Â C15**.

#### 2.2. Utilize COUNTIFS for Partial Match with Multiple Criteria

For this example, I have taken the following dataset. Here, I will find a partial match for multiple criteria using the **COUNTIFS** function in Excel.

Letâ€™s see the steps.

**Steps:**

- Firstly, select the cell where you want the
**No. of Books**. - Secondly, write the following formula in that selected cell.

`=COUNTIFS(D5:D12,"*"&C15&"*",D5:D12,"*"&C16&"*")`

- Thirdly, press
**Enter**to get the**No. of Books**.

**COUNTIFS**function, I selected the cell range

**D5:D12**as criteria_range1,

**“*”&C15&”*”**as criteria1,

**D5:D12**as criteria_range2, and

**“*”&C16&”*”**as criteria2. The formula will return the number of cells that partially match the value in cell

**Â C15**and in cell

**Â C16**.

### 3. Insert IF and COUNTIF Functions for Partial Match in Excel

Apart from the count result, you can produce results of whether the value is present or not. For example, I will compare a given** Genre **to each of the **Genres** from the following dataset. And return if it contains the given **Genre** or not.

Let me show you the steps.

**Steps:**

- In the beginning, select the cell where you want the
**Status**. Here, I selected cell**Â D5**. - Next, in cell
**Â D5**write the following formula.

`=IF(COUNTIF(C5,"*"&$F$5&"*"),"Genre Found","Not in search Genre")`

- After that, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**COUNTIF(C5,”*”&$F$5&”*”):**Here, the**COUNTIF**function returns the number of cells that match the criteria.**IF(COUNTIF(C5,”*”&$F$5&”*”),”Genre Found”,”Not in search Genre”):**Now, the**IF**function checks for the**logical_test**. If it is**True**then it returns**“Genre Found”**and if it is**False**then it returns**“Not in search Genre”**.

- Next, drag the
**Fill Handle**down to copy the formula.

- In the end, you can see that I have copied the formula to the other cells and got my desired output.

## Alternative to COUNTIF Function for Partial Match in Excel

Instead of the **COUNTIF **function, you can use a combination of several functions to perform the same type of operation. This alternative formula can be created using the **SUMPRODUCT** function, the **ISNUMBER** function, and the **SEARCH** function. Letâ€™s see the steps.

**Steps:**

- Firstly, select the cell where you want the
**No. of Books**. - Secondly, write the following formula in that selected cell.

`=SUMPRODUCT(--(ISNUMBER(SEARCH(C15,D5:D12))))`

- Thirdly, press
**Enter**to get the**No. of Books**.

**🔎** **How Does the Formula Work?**

**SEARCH(C15,D5:D12):**Here, the**SEARCH**function, looks for the criteria value inside the range. As you know this function returns the starting position of the**searh_text**if find, error otherwise.**ISNUMBER(SEARCH(C15,D5:D12)):**The**ISNUMBER**function checks whether the**SEARCH**functionâ€™s result is a number or not. It returns a**boolean value**.**–(ISNUMBER(SEARCH(C15,D5:D12))):**To convert this**boolean value**into**1**or**0****two Unary Operators (–)**have been used ahead of it.**SUMPRODUCT(–(ISNUMBER(SEARCH(C15,D5:D12)))):**Finally,**the SUMPRODUCT function**finds an array within it and returns the total**sum**.

**Instead of**

*Note:***SUMPRODUCT**, you can use

**the SUM function**. But then you need to press

**CTRL**+

**SHIFT**+

**ENTER**to execute the formula.

## Practice Section

Here, I have provided a practice sheet for you to practice how to use the **COUNTIF **function for a partial match in Excel.

**Download Practice Workbook**

You are welcome to download the practice workbook from the link below.

## Conclusion

Thatâ€™s all for today. I have listed a couple of approaches using **COUNTIF **in partial matching. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let me know any other methods that I might have missed here.

**<< Go Back to Partial Match Excel | Formula List | Learn Excel**