COUNTIF Partial Match (Couple of Easy 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.

Dataset - COUNTIF Partial Match

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.

Partial Match with COUNTIF

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.

Criteria field - COUNTIF Partial Match

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.

Criteria value - COUNTIF Partial Match

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

=COUNTIF(B4:E11,"*"&H4&"*")

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

COUNTIF formula - COUNTIF Partial Match

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.

COUNTIF formula result - COUNTIF Partial 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

=COUNTIF(D4:D11,"*"&H4&"*")

Optimized COUNTIF Formula - COUNTIF Partial Match

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

Optimized COUNTIF formula result - COUNTIF Partial Match

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

Change of values - COUNTIF Partial Match

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

=COUNTIFS(D4:D11,"*"&H4&"*")

COUNTIFS - COUNTIF Partial Match

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.

COUNTIFS formula result - COUNTIF Partial Match

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

 =COUNTIFS(D4:D11,"*"&H4&"*",D4:D11,"*"&I4&"*")

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.

COUNTIFS in two criteria formula - COUNTIF Partial Match

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.

COUNTIFS two criteria value result- COUNTIF Partial Match

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.

User friendly example dataset - COUNTIF Partial Match

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

 =IF(COUNTIF(D4,"*"&$I$4&"*"),"Genre Found","Not in search Genre")

COUNTIF - IF Formula - COUNTIF Partial Match

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.  

COUNTIF - IF Formula result - COUNTIF Partial Match

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

COUNTIF - IF Formula 2 - COUNTIF Partial Match

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

COUNTIF - IF Formula AutoFill- COUNTIF Partial Match

An Alternative Approach

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

 =SUMPRODUCT(--(ISNUMBER(SEARCH(H4,D4:D11))))

SUMPRODUCT formula - COUNTIF Partial Match

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.

SUMPRODUCT Formula result - COUNTIF Partial Match

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

SUM array formula - COUNTIF Partial Match

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.

shakil

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

ExcelDemy
Logo