COUNTIF Partial Match in Excel (2 or More Approaches)

COUNTIF 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 we are going to show you how to use the COUNTIF function for a partial match. For this session we are using Excel 2019, feel free to use yours.

First things first, let’s get to know about today’s workbook.

Here we have a table that contains several books along with their respective details. We will perform the partial match.

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

Practice Workbook

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

Ways to COUNTIF Partial Match

1. Count upon finding the match

As the name suggests the COUNTIF functions count items that match the provided criteria. Just for a reminder, check the COUNTIF article.

Here we will set the criteria and count when the match is found. For example, we have set the criteria value as a Thriller genre.

I. Use COUNTIF Function

Since our agenda is to perform a partial match we will use the asterisk sign (*) in the criteria field as a wild card.

Now our formula will be

H4 is the cell reference of our criteria value. And we have added a couple of asterisks on either side of the criteria.

The ampersand (&) sign is for concatenating the criteria value and the wild cards. These wild cards denote that any number of characters (including 0) can take place.

B4:E11 is the range where to look for the match.

Here we have searched for Thriller and the result of the formula is 5.

You can see the original table has only a couple of rows where the genre is “Thriller”. But the formula also counted values like “Psychological Thriller”, “Mystery/ Thriller” and provided the result.

We can optimize the formula by the following approach

Here we have selected only the column where the criteria value can be found. The result will be the same.

Change the criteria value and the result will be updated automatically.

II. Use a COUNTIF Family Function

Similar to COUNTIF we 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. To know more, visit the COUNTIFS article.

The formula will be similar to the previous one

Here we have used the COUNTIFS function in place of the COUNTIF, other values remain the same. And the result will be the expected one.

In addition, we can check other criteria. Let’s check for another genre value Thriller. Now let’s insert the criteria within COUNTIFS.

The formula will be the following one

Here we have Inserted the range D4:D11 a couple of times since both criteria values would be found within this range and set the criteria value using their respective cell references.

When we set a couple of values then it will count as an AND operation. Here we have checked for Horror and Thriller genres, this means only that value will be counted where both Horror and Thriller are present.

2. User Understandable Result

Apart from the count result, we can produce user understandable results of whether the value is present or not.

For example, we will compare a given genre to each of the genres from our dataset.

To produce a realizable result, we first need to use the IF function along with COUNTIF (or COUNTIFS).

The IF function runs a logical test and returns a boolean value. For further information, visit the IF article.

Now let’s explore the formula

Here “Genre Found” is the if_true_value and “Not in search Genre” is the if_false_value.

Our provided genre is Thriller and the listed genre is Horror, so it will return FALSE and the if_false_value will be the result.  

Dive Deeper: Partial Match with IF in Excel (4 Basic Operations)

When the listed genre has Thriller in it, the formula will return the if_true_value.

Write the formula for the rest of the values or exercise the AutoFill feature.

An Alternative Approach to COUNTIF Partial Match

Instead of the COUNTIF function, we can use a combination of several functions to perform the same type of operation.

This alternative formula will be created using SUMPRODUCT, ISNUMBER, and SEARCH functions. We can use FIND in place of SEARCH to make it case-sensitive.

To know about these functions, visit these articles;  SUMPRODUCT, ISNUMBER, and SEARCH.

The formula will be

Within the SEARCH function, we looked for the criteria value inside the range. As you know this function returns the starting position of the searh_text if find, error otherwise.

The ISNUMBER function checks whether the SEARCH function’s result is a number or not. It returns a boolean value. To convert this boolean value into 1 or 0 two unary operators (--) have been used ahead of it.

Then the SUMPRODUCT function finds an array within it and returns the total sum.

Instead of SUMPRODUCT, you can use the SUM function. But then you need to press CTRL + SHIFT + ENTER to execute the formula.

Conclusion

That’s all for today. We 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 us know any other methods that we might have missed here.

Further Readings

• How to Use INDEX and Match for Partial Match (2 Ways)
• Excel Partial Match Two Columns (4 Simple Approaches)
• How to Use VLOOKUP for Partial Match in Excel (4 Ways)
• How to Perform Partial Match String in Excel (5 Methods)
• Lookup Partial Text Match in Excel (5 Methods)
• VLOOKUP Partial Text from a Single Cell in Excel