How to Filter with Multiple Criteria in Excel (4 Easy Ways)

Excel FILTER Function: Overview

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.

Let’s get the years when Brazil became the champion.

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 condition is not met for any cell. The resulting syntax is:

One of the formulas you can use to get the years when Brazil was the champion is:

D5:D25=”Brazil” goes through all the cells from D5 to D25 and returns a TRUE if it finds Brazil. If not, the result is FALSE, and the function ignores the cell.

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

For each TRUE value, FILTER returns the corresponding cell from the array {B5,B6,B7,…,B25}

We get TRUE only for 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.

As another example, here’s a formula for determining when the host country was the champion:

The host country became champion in 1930, 1934, 1966, 1974, 1978, and 1998.

We’ll use the same dataset to apply multiple criteria in the FILTER function.

Method 1 – Using the FILTER Function with Multiple OR Criteria

Let’s filter out all the years when Italy was the host or the champion, or both. This is an OR-type multiple criteria.

Steps:

• Select cell G5 and insert the following formula, then hit Enter.

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

Here’s the formula for determining when Brazil became the champion or Italy became the runner-up:

Method 2 – Applying the FILTER Function with Multiple AND-Type Criteria

Up until 1970, the FIFA World Cup was called the “Jules Rimet” trophy. After 1970, it was renamed the FIFA World Cup. What are the years when Brazil won the “Jules Rimet” trophy?

There are two criteria here:

• The year must be less than or equal to 1970.
• The champion country has to be Brazil.

This is an AND-type condition, where both have to be met simultaneously.

Steps:

• Select cell G5 and insert the following formula, then hit Enter.

Here’s the formula to look up the years before 2000 when Brazil was the champion and Italy was the runner-up:

Method 3 – Filtering Multiple Criteria by Combining AND and OR Conditions in Excel

Case 1 – OR within OR

Let’s find the years when a South American country (Brazil, Argentina, or Uruguay) was either a champion or a runner-up.

Steps:

• Select cell G5 and insert the following formula, then hit Enter.

Case 2 – OR within AND

Let’s determine the years when both the champion and runner-up were from South America (Brazil, Argentina, or Uruguay).

• Select cell G5 and insert the following formula, then hit Enter.

Method 4 – Inserting the FILTER Function in Multiple Columns

Let’s identify the years when Germany was the champion. Before 1990, the country that participated was West Germany, so we’ll count it as well.

• Select cell G5 and insert the following formula, then hit Enter.

Let’s see when the FIFA World Cup was hosted by two countries. For this, the table contains ” and “ in the host country name.

• Select cell G5 and insert the following formula, then hit Enter.

Alternative Options to Filter Multiple Criteria in Excel

The FILTER function is available only for Office 365. Here are some alternatives:

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

• To find the years when Brazil was champion up to 1970, use this formula:

How to Use the Advanced Filter in Excel

We’ll apply multiple criteria on one column using calculated data. We’re going to find delivered products with quantity more than 50 but less than 100.

• Apply the following formula:

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

• Select the Advanced command under the Sort & Filter options from the Data tab.
• Put the whole dataset as the List range and cells C15:C16 as the Criteria range.

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

Download the Practice Workbook

<< Go Back to Filter in Excel | Learn Excel