How to Use COUNTIF Function for Partial Match in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset for Using COUNTIF Function for Partial Match in Excel


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.

Use COUNTIF Function for Partial Match in Excel

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&"*")

Employ COUNTIF Family Function for Partial Match in Excel

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

Utilize COUNTIFS for Partial Match with Multiple Criteria

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.

Insert IF and COUNTIF Functions for Partial Match in Excel

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.

Dragging Fill Handle to Copy COUNTIF fotmula for Pertial Match in Excel

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

Alternative to COUNTIF Function for Partial Match in Excel

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

Practice Sheet for COUNTIF for Pertial Match


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo