Today I will be showing you how Excel filter multiple criteria of some matching data using the FILTER function of Excel. Before going to the main discussion, I would like to remind you of one thing. The **FILTER** function is only available in Office 365.

**Table of Contents**Expand

## Excel FILTER Function: Overview

Let’s be introduced to Excel’s **FILTER** function first to filter multiple criteria.

Look at the data set below. We have the years, the **host countries**, the **champion countries**, and the **runners-up countries** of all the **FIFA World Cups** in columns **B, C, D,** and **E** respectively.

Now if I ask you, what are the years when **Brazil **became the champion?

What will you do?

You will probably go through column **D** (Champion), and see whether there is a cell containing a **Brazil **in it or not.

Then when you find one, you will move two steps left of that cell to column **B** (Year), and note down the corresponding year.

And then you will again go down through column **D** and do the same for all the cells containing a **Brazil **in it.

Thus, you will note down all the years when **Brazil **was the champion.

For a small set of data, this is **OK**. But can you repeat the same procedure for a large set of data, think, of **10000** rows?

The answer is no, a big no.

So what to do?

Microsoft Excel brings a built-in function called **FILTER** to perform exactly the same task for you.

The **FILTER** function takes three arguments, a range of cells called an **array**, a criterion called **include, **and a value called** if_empty **that is returned in case the criterion is not met for any cell.

So the syntax of the **FILTER** function is:

`=FILTER(array,include,[if_empty])`

For a better understanding, let’s come to the **Brazil **problem. We have to filter out the years when **Brazil **became the champion.

The formula to accomplish this will be:

`=FILTER(B5:B25,D5:D25="Brazil","")`

See, we have got all the years when **Brazil **became champion, **1958, 1962,1970, 1994, **and **2002** (Colored in the Image).

Now for the sake of understanding, let’s break down the formula.

**D5:D25=”Brazil” **goes through all the cells from **D5** to **D25** and returns a **TRUE** if it finds a **Brazil**, otherwise **FALSE**.

The formula **FILTER(B5:B25,D5:D25=”Brazil”,””) **then becomes

`=FILTER({B5,B6,B7,...,B25},{FALSE,FALSE,...,TRUE,...,FALSE},"")`

For each **TRUE**, it returns the adjacent cell from the array **{B5,B6,B7,…,B25}**

And for a **FALSE**, it returns no result,** “”**. (This is optional. Default is no result, **“”**)

There is a **TRUE** for only the cells **B9**, **B10**, **B12**, **B18,** and **B20**.

So it returns only the contents of these cells, 1958, 1962, 1970, 1994, and 2002.

These are the years when Brazil became the champion.

Hope you have understood how the** FILTER** function works.

Now, if you understand this, can you tell me the formula to find out the years when the host country became the champion?

Yes. You are right. The formula is:

**=FILTER(B5:B25,C5:C25=D5:D25,””)**

See, the host country became champion in **1930, 1934, 1966, 1974, 1978, **and **1998.**

Now we have understood how the **FILTER** function works. Let’s try to apply multiple criteria within the function this time. Here’s an overview of the dataset for today’s task.

## 1. Using Excel FILTER Function with Multiple OR Type Criteria

First of all, let’s focus on multiple criteria of **OR **type. These are the criteria that are satisfied when any one or more than one criteria are satisfied.

For example, from the above data set, if I ask you, tell me one year when **Argentina **became **champion **or **West Germany** became **runners-up**.

You can tell either **1978**, or **1982 **or **1986**.

Now, let’s try to **filter out** all the years when **Italy **was either the **host **or the **champion**, or **both**. This is a problem of **OR **type multiple criteria. It is an easy task. Just add the two criteria with a plus **(+)** sign. Let’s follow the instructions below to filter multiple criteria in Excel!

**Steps:**

- First of all, select cell
**G5**, and write down**the FILTER function**in that cell. The function will be:

`=FILTER(B5:B25,(C5:C25="Italy")+(D5:D25="Italy"))`

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the years when Italy was the**host**or**champion**or**both**which is the return of**the FILTER function**.

See, **Italy **was either the host or champion or both in the years **1934, 1938, 1982, 1990, **and **2006.**

**Formula Breakdown**

Now, for the sake of understanding, let’s break down the formula.

**C5:C25=”Italy”**returns an array of**TRUE**or**FALSE.****TRUE**when Italy was the host,**FALSE**otherwise.**D5:D25=”Italy”**also returns an array of**TRUE**or**FALSE**.**TRUE**when Italy was the champion,**FALSE**otherwise.**(C5:C25=”Italy”)+(D5:D25=”Italy”)**adds two arrays of Boolean values,**TRUE**and**FALSE**. But it considers each**TRUE**as a**1**, and each**FALSE**as a**0**.- So it returns a
**2**when both criteria are satisfied, a**1**when only one criterion is satisfied, and a**0**when no criterion is satisfied.

The formula now becomes:

`=FILTER({B5,B6,B7,...,B25},{0,2,1,...,0})`

It considers the numbers greater than zero (0 and 1 here) as **TRUE** and the zeros as **FALSE.**

So it returns the years from column** B** when it faces a number greater than **0** and returns no result otherwise.

Now, if you understand how the **FILTER** function works with multiple criteria of OR type, can you give an answer to one question?

What will be the formula to filter out the years when Brazil became the champion or Italy became the **runners-up** or both?

Yes. You are right. The formula will be:

`=FILTER(B5:B25,(D5:D25="Brazil")+(E5:E25="Italy"))`

## 2. Applying Excel FILTER Function with Multiple AND Type Criteria

Now we shall focus on **multiple criteria** of **AND **types. That means we have to satisfy all the criteria to get a **TRUE** result, otherwise **FALSE**.

We know, up to the year **1970**, the **FIFA world cup** was called the **“Jules Rimet”** trophy. After **1970**, it started to be named the **FIFA world cup**. So my first question is, what are the years when **Brazil **won the** “Jules Rimet”** trophy?

There are** two criteria** here.

- First, the year must be less than or equal to
**1970**. - Second, the
**champion**country has to be**Brazil**.

And both criteria are to be met. How to accomplish this task?

Quite simple. Multiply the two criteria inside the **FILTER** function with a **(*)** sign this time. Let’s follow the instructions below to filter multiple criteria in Excel!

**Steps:**

- First of all, select cell
**G5**, and write down**the FILTER function**in that cell. The function will be:

`=FILTER(B5:B25,(B5:B25<=1970)*(D5:D25="Brazil"))`

**Formula Breakdown**

returns a`(B5:B25<=1970`

**TRUE**if the year is less than or equal to 1970, otherwise**FALSE**.returns a`(D5:D25="Brazil")`

**TRUE**if the champion country is Brazil, otherwise**FALSE.**multiplies two arrays of`(B5:B25<=1970)*(D5:D25="Brazil")`

**TRUE**and**FALSE**, but considers each**TRUE**as**1**and each**FALSE**as**0**.- So it returns a
**1**if both the criteria are met, otherwise it returns a**0.** - Now the formula becomes:
`=FILTER({B4,B5,B6,...,B24},{0,0,...,1,1,...,0})`

- It returns the year in column
**B**when it faces a**1**and returns no result when it faces a**0**.

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the years when**Brazil**was the**champion**of the**“Jules Rimet”**trophy which is the return of**the FILTER function**. See, up to**1970**, Brazil won**three times**, in**1958, 1962,**and**1970**.

Thus we can filter any data satisfying multiple criteria of **AND** type.

Now can you tell me the formula to find out the years before **2000** when **Brazil** was the champion and Italy was the runners-up?

The formula will be:

`=FILTER(B5:B25,(B5:B25<2000)*(D5:D25="Brazil")*(E5:E25="Italy"))`

## 3. Filtering Multiple Criteria Combining AND and OR Types in Excel

### Case 1: OR within OR

Now if I ask you a question, what are the years when a **South American **country (**Brazil, Argentina, or Uruguay**) was either **champion **or** runners-up**?

Can you give the answer to my question?

Notice carefully. Here the champion country has to be **Brazil, Argentina, **or **Uruguay**. Or the Runners-Up country has to be **Brazil, Argentina, or Uruguay**. Or both. This is a problem of OR within OR type. Do not worry simply follow the instructions below to filter multiple criteria in Excel!

**Steps:**

- First of all, select cell
**G5**, and write down**the functions**in that cell. The functions will be:

`=FILTER(B5:B25,(ISNUMBER(MATCH(D5:D25,{"Brazil","Argentina","Uruguay"},0)))+ (ISNUMBER(MATCH(E5:E25,{"Brazil","Argentina","Uruguay"},0))))`

**Formula Breakdown**

returns`MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)`

**1**if the champion team is Brazil,**2**if the champion team is Argentina,**3**if the champion team is Uruguay, and an error**(N/A)**if the champion team is none of them.converts the numbers into`ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0))`

**TRUE**and the errors into**FALSE**.- Similarly,
returns a`ISNUMBER(MATCH(E4:E24,{"Brazil","Argentina","Uruguay"},0))`

**TRUE**if the runners-up country is either Brazil, Argentina or Uruguay. And**FALSE** - So,
returns a`(ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)))+(ISNUMBER(MATCH(E4:E24,{"Brazil","Argentina","Uruguay"},0)))`

**1**or**2**if either a South American country is champion, or runners up, or both. - And returns zero otherwise.
- The formula becomes:
`=FILTER({B4,B5,...,B24},{2,0,0,2,...,1,0})`

- It returns a year from column
**B**if it finds a number greater than zero, and returns no result otherwise.

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the years when a**South American**country (**Brazil, Argentina, or Uruguay**) was either**champion**or**runners-up.**See, we have found all the years when a South American country was either champion or runners-up.

### Case 2: OR within AND

If you understand the above formula, can you tell the formula to determine the years when both the **champion and runners-up** were from **South America (Brazil, Argentina, **or **Uruguay)**?

Quite easy. Just replace the **(+)** sign of the previous formula with an **(*)** sign. The functions are:

`=FILTER(B4:B24,(ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)))*(ISNUMBER(MATCH(E4:E24,{"Brazil","Argentina","Uruguay"},0))))`

See, these happened only twice, in 1930 and 1950.

## 4. Inserting Excel FILTER Function in Multiple Columns

Now if you notice more carefully, you will find that up to the year **1990**, there was a country called **West Germany**. And after **1990**, there is no **West Germany**. What there is **Germany**. The two are actually from the same country. In **1990**, the **two Germany (East **and **West)** united to form present Germany.

Now can you identify the years when **Germany **was the **champion**? No matter **East **or **West**.

You have to utilize the FILTER function in multiple columns.

The formula will be:

`=FILTER(B5:B25,ISNUMBER(SEARCH("*Germany",D5:D25)))`

**Formula Breakdown**

searches for anything having Germany in the end in the array`SEARCH("*Germany",D5:D25)`

**D5**to**D25**. If you need Germany in the middle, use**“*Germany*”.**- It returns a
**1**if it finds a match (West Germany and Germany) and returns an**Error** converts the 1’s into`ISNUMBER(SEARCH("*Germany",D5:D25))`

**TRUE**, and the errors into**FALSE**.- Finally,
returns the years from column`FILTER(B5:B25,ISNUMBER(SEARCH("*Germany",D5:D25)))`

**B**when it faces a**TRUE**, otherwise returns no result.

- See Germany was the champion
**4 times**.**3**times by**West Germany**and**1**time by present**Germany**.

Now, if you understand this formula, can you find out the years when the **FIFA World Cup** was hosted by **two countries**?

I am giving you a clue. There must be an **” and “** in the host country name. (**“and”** in-between two spaces)

Yes. You are right. The formula will be:

`=FILTER(B5:B25,ISNUMBER(SEARCH("* and *",C5:C25)))`

Now, we see this happened only once in **2002**, hosted by **South Korea **and **Japan**.

**Alternative Options to Filter Multiple Criteria in Excel**

The methods mentioned above about filtering multiple criteria are quite useful. But with **one disadvantage**, the **FILTER **function is available in only **Office 365**.

Those who do not have an Office 365 subscription, can use these alternative methods to filter some data with multiple criteria.

To find out the years when **Italy **was the **host country or champion**, use the below formula:

`=IF((C5:C25="Italy")+(D5:D25="Italy"),B4:B24,"")`

And to find out the years when **Brazil **was champion up to **1970**, use this formula:

`=IF((B5:B25<=1970)*(D5:D25="Brazil"),B5:B25,"")`

**Note: ***You can not have the blank cells removed like the FILTER function in this way. And press Ctrl + Shift + Enter to enter the formulas.*

## How to Use Advanced Filter in Excel

We’ll apply **multiple criteria **on **one column **using **calculated data**. Here, we’re going to **find** **delivered products **with **quantity more than 50 **but **less than 100**. For this, we need to **apply **the following **formula**. The **formula **is-

`=IF(AND(E5<100,E5>50),E5,FALSE)`

The output in cell **C16 **is **55 **as the **delivered quantity falls **in the **range**.

Hence, select the **Advanced** command under the **Sort & Filter** options from the **Data **tab.

After that, we put the **whole dataset **as the **List range **and **cells C15:C16** as the **Criteria range**.

Finally, hit **OK **to see the **result, **i.e., a list of **delivered products **having a **quantity **in the range **from 50 to 100.**

**Download Practice Workbook**

## Conclusion

Using these methods, you can filter out any data by maintaining multiple criteria in Excel. Do you know any other method? Let us know in the comment section.

**<< Go Back to Filter in Excel | Learn Excel**

Sir, 28th July,2021.

I am impressed by the example given by you,i.e. Filter Function.

Hope to receive more functions in future too.

Thanking you.

Kanhaiyalal Newaskar.

This is great!

Thanks

M