Filter with Multiple Criteria

If you are an valid MS Excel user, you have probably come across a situation where you wanted to filter the data in a separate table with complex criteria. You could do this task manually, which is also acceptable while dealing with a few data items.

But if you got a task to filter out multiple items from a table consisting of a lot of data along with specific complex criteria, then doing these kinds of tasks manually would definitely be a stupid decision because this would not only waste your precious time but you would also get tired of it and won’t complete your task on time.

But don’t be worry about it; for getting out of this fix and filtering out multiple data with complex criteria, all you have to do is read this article carefully.

filter with multiple criteria1

So let’s dive into it.

General formula:

The formula below would help you filter out multiple data with complex criteria within a few seconds.

As we have altered the following formula according to the example which we would discuss in this article to understand that how this formula works and how to use this formula:

=FILTER(A2:D9,(LEFT(A2:A9)="e")*(C2:C9="East"))

In the formula stated above, we use the filter function and a series of boolean logic expressions.

filter with multiple criteria1

Let’s See How This Formula Works

In this example, we must create logic that filters data to contain the following conditions: product begins with “e” AND Region is “East“.

This formula’s filtering logic (the include argument) is built by chaining together two expressions that employ boolean logic on arrays in the data. The first expression used the LEFT function to determine whether product name begins with “e“:

=LEFT(A2:A9)="e" // the product name starts with "e"

As a consequence, an array of TRUE FALSE values looks like this:

filter with multiple criteria1

The equal to (=) operator is used in the second equation to see if Region is “East“:

=C2:C9="East"  //the region is to the East

As a consequence, another array is created:

filter with multiple criteria1

=(LEFT(A2:A9)="e")*(C2:C9="East")

The sum of the two arrays is calculated. Because the math process converts TRUE and FALSE values to 1s and 0s.

Because Boolean multiplication corresponds to the logical operator AND, the result is a single array that looks like this:

filter with multiple criteria1

=FILTER(A2:D9, {1;0;0;0;0;0;0;0})

The FILTER function filters the data using this array, returning the one rows that correspond to the 1s in the array.

filter with multiple criteria1

Advanced Filter allows you to filter data based on numerous criteria

Assume I have the following data list that has to be filtered using several criteria:

  • Product = “excel” and region = “East”,
  • The product is “access”, and employee name is “nora”.
  • The product is word, and the sales is greater than 200.

And the link is OR among the three criteria.

filter with multiple criteria1

Please follow the procedures below when using the Advanced Filter function:

Step1: Create your criterion field in an area.See the following screenshot:

filter with multiple criteria1

Step2: Select the data range to be filtered and go to Data > Advanced, as shown in the screenshot:

filter with multiple criteria1

Step3: Finally, in the Advanced Filter dialogue box, click the button next to Criteria range to choose the criteria that I just set, as seen in the screenshot:

filter with multiple criteria1

Step4: Then click OK, and the filtered results are displayed; entries that do not meet the criteria are hidden. See the following screenshot:

filter with multiple criteria1

Related Functions

  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
Related Posts

Abbreviate Names Or Words in Excel
abbreviate names1

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn't any ...

Extract substring In Excel
Extract substring In Excel1

This post will guide you how to use Excel's MID function is a quick and easy way to extract pieces from your text. Use the Excel formula to extract a substring with MID. Note: If you want to extract just ...

How to Split Cells by the First Space in Texts in Excel
How to Split Cells by the First Space in Texts in Excel8

Sometimes we may use space to separate texts to different groups in one cell, if we want to split this cell to multiple columns refer to space, we can implement this via ‘Text to Columns’ feature. But is there any ...

How to Convert Uppercase to Lowercase Except the First Letter in Excel
Convert Uppercase to Lowercase 4

In excel, words can be entered in uppercase, lowercase or mixed. If we want to convert all uppercase to lowercase (except the first letter in some situations), we can use formula with some letter convert related functions to convert them ...

How to Remove Middle Name from Full Name in Excel
Remove Middle Name from Full Name 6

When we get a list of full names, we can remove the middle name for short in some cases. As the middle names are different among the full name list so we cannot replace them by space directly in excel. ...

How to Convert Text to Time in Excel
convert text to time7

This post will guide you how to convert time string to time in Excel. Assuming that you have a list of text string in your worksheet, and you wish to convert these time string to standard time format, how to ...

How to Extract Initials From a Name in Excel
extract initials from a name2

This post will guide you how to get initials from a given name using a formula in Excel. How do I extract initials from names in Excel 2013/2016. Extract Initials from a Name Using a Formula Extract Initials from a ...

How to Remove Last or Trailing Commas in Excel
remove last commas1

This post will guide you how to remove trailing commas from cell in Excel. How do I remove comma character at the end of a cell with a formula in Excel 2013/2016. Remove Trailing Commas if you want to remove ...

How to Filter Cells Starts with Number or Letter in Excel

This post will guide you how to filter values starts with number or letter in one single cell in Excel. How do I filter in a list those that begin with number or letter using a formula in Excel. Filter ...

How to Convert mmddyy to Date in Excel
convert mmddyyy to date9

This post will guide you how to convert non-standard date formats or text to a standard date in Excel. If you have a date with mmddyy format, and you want to change it as the normal date format as mm/dd/yyyy ...

Sidebar