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

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

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

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

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

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

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

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

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

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

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

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

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