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