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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Introduction to FILTER Function

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.

Introduction to FILTER Function


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.

Filter Multiple Values of OR Type

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"))

Filter Multiple Values of OR Type


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

  • (B5:B25<=1970 returns a TRUE if the year is less than or equal to 1970, otherwise FALSE.
  • (D5:D25="Brazil") returns a TRUE if the champion country is Brazil, otherwise FALSE.
  • (B5:B25<=1970)*(D5:D25="Brazil") multiplies two arrays of 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.

Apply FILTER Function For AND Type

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

  • MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0) returns 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.
  • ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)) converts the numbers into TRUE and the errors into FALSE.
  • Similarly, ISNUMBER(MATCH(E4:E24,{"Brazil","Argentina","Uruguay"},0)) returns a TRUE if the runners-up country is either Brazil, Argentina or Uruguay. And FALSE
  • So, (ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)))+(ISNUMBER(MATCH(E4:E24,{"Brazil","Argentina","Uruguay"},0))) returns a 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.

Filter Multiple Criteria with Combination of AND and OR Type in Excel


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

Filter Multiple Criteria with Combination of AND and OR Type in Excel

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

  • SEARCH("*Germany",D5:D25) searches for anything having Germany in the end in the array 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
  • ISNUMBER(SEARCH("*Germany",D5:D25)) converts the 1’s into TRUE, and the errors into FALSE.
  • Finally, FILTER(B5:B25,ISNUMBER(SEARCH("*Germany",D5:D25))) returns the years from column 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.

Filter Multiple Criteria with Combination of AND and OR Type in Excel

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

Filter Multiple Criteria with Combination of AND and OR Type in Excel

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,"")

Alternative Options to Filter Multiple Criteria in Excel

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,"")

Alternative Options to Filter Multiple Criteria in Excel

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.

Advanced Filter in Excel

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. Reply
    Kanhaiyalal Newaskar. Jul 28, 2021 at 10:55 AM

    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.

  2. This is great!
    Thanks
    M

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo