# How to Use COUNTIF Function for Partial Match in Excel

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.

Here, in the 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.

Here, in the 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.

Here, in the 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.
Note: Instead of 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

Get FREE Advanced Excel Exercises with Solutions!
Shakil Ahmed

My nameâ€™s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF