How To Use Excel FILTER Function With Multiple Criteria

Free Practice File

Download the Practice File

The exact workbook from the video with all FILTER formula examples and sample data. Follow along and test every criteria combination yourself.

• Sample Data
• Formula Examples
• Finished Solutions

Download the Practice File

No spam. Unsubscribe anytime.

Excel FILTER Function with Multiple Criteria (AND Logic)

Let’s start by examining the data.  We have a 4-column table formatted as an official Excel Data Table named “TableDiv”.

Our objective is to filter the table to only show rows where the user works in both the “Productivity” [Division] AND is a member of the “Finance” [Department].

Start our formula using the FILTER Function.

=FILTER(

We will select the entire table named “TableDiv”…

=FILTER(TableDiv,

This returns the entire row of each qualifying Division/Department.  We’ll see in a little bit how to return only select columns from the filter matches.

Now we define our filter criteria to only include rows where the [Division] is equal to “Productivity” (the “Productivity” choice is in cell G1).

=FILTER(TableDiv, TableDiv[Division]=G1)

The results are as follows.

This has reduced the table to only Divisions that match “Productivity”.  We must now reduce the list further to only include Departments that match “Finance” (the “Finance” choice is in cell G2).

The new formula is…

=FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) )

If you’re wondering why you are multiplying two filters against one another, then you are on the right track.

By selecting (i.e., highlighting) the first filter in the formula, we are presented with a list of “true/false” responses.

This appears automatically in the latest version of Microsoft 365. If you are running an earlier version of Excel, press the F9 key to see this list of responses

❗Make sure you press ESC to get out of the formula to not permanently change the formula.

The “true/false” responses indicate on an item-by-item level which [Division] entries match “Productivity” and which do not.

We can perform the same check on the second filter in the formula.

The reason we multiply these results against one another is that the moment you perform any mathematical operation on Boolean values, the “true/false” responses are changed to “1/0” responses.

This means, if you multiply a “1” by another “1”, you get “1” (true).

Any other combination of (1 * 0), (0 * 1), or (0 * 0) will result in a “0” (false).

This means a full match can only occur when all tests are resulting in a “1”.  Any “0” introduced into the logic will result in a “0”, effectively disqualifying the full set of tests from the result list.

Selecting both filter tests in the formula produces a list of ones and zeroes.

{0;0;1;0;1;0;0;0;0;0;0;1;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

Ultimately, the full set of tests only returns rows that evaluate to a 1 (true).

You could build on this logic to only include rows where the [Yearly Salary] are greater than $100,000.

=FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) * (TableDiv[Yearly Salary] > G3) )

Featured Course

Master Excel’s Essential Modern Functions

FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE. The Excel functions most professionals were never taught, and the ones that turn hours of work into minutes.

Learn More

Excel FILTER Function with Multiple Criteria (OR Logic)

What if instead of a row in the table having to match ALL the defined filter criteria, we accept table rows where some (or all) criteria match? 

In other words, any row from the “Productivity” Division along with any row from the “Finance” Department.

This is a simple matter of replacing the multiply operators in the formula with addition operators.

This changes the results of the row-by-row tests.

{1;1;2;1;2;1;1;1;0;0;0;2;1;2;1;1;0;0;0;0;1;1;1;0;0;1;0;1;0;0;1}

The Boolean math says that if there is a “1” (true) anywhere in the result set, the result is “true”.  Any value other than 0 (zero) is considered a “true” by Excel.

How to Return Selected Columns with Excel FILTER

Using our previous example of filtering for “Productivity” or “Finance”, suppose we only wish to return the [Name] and [Yearly Salary] columns.

There are tricks you can use when working with the FILTER Function (check them out in the post “Excel FILTER Function TRICK for Non-Adjacent Columns”).

Use the CHOOSECOLS function to select specific columns from the filtered results.

Taking our filtered result from the earlier formula:

=FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) )

Wrap this formula inside a CHOOSECOLS Function.

=CHOOSECOLS(FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) ), 2, 4)

The CHOOSECOLS Function lets us select the second and fourth columns from the filtered results.

We can take those results to another level by sorting them in descending order by [Yearly Salary].  This is done by wrapping the current formula within a SORT Function.

=SORT(CHOOSECOLS(FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) ), 2, 4), 2, -1)

The “2” in the SORT Function indicates the column to sort by, while the “-1” indicates the sort direction (descending).

FILTER Function: Multiple Criteria in the Same Column

It’s time for a challenge.

Suppose you wish to filter the data using the following criteria:

• The [Division] has to be either “Game” or “Utility”
• The [Yearly Salary] is greater than $80,000
• We only want the [Name] and [Yearly Salary] columns
• The results must be sorted by [Yearly Salary] in descending order

For the first requirement, we’ll use the FILTER Function and an OR operator against the first column ([Division]) of the data.

=FILTER(TableDiv, (TableDiv[Division]=G1) + (TableDiv[Division]=G2) )

For the second requirement, we will reduce the data to only include [Yearly Salary] entries that are greater than $80,000.

=FILTER(TableDiv, ( (TableDiv[Division]=G1) + (TableDiv[Division]=G2) ) * TableDiv[Yearly Salary] > G3)

Next, we will select only the 2^nd and 4^th columns from the table using the CHOOSECOLS Function.

=CHOOSECOLS(FILTER(TableDiv, ( (TableDiv[Division]=G1) + (TableDiv[Division]=G2) ) * TableDiv[Yearly Salary] > G3), 2, 4)

Finally, we sort the list in descending order by the 2^nd column.

=SORT(CHOOSECOLS(FILTER(TableDiv, ( (TableDiv[Division]=G1) + (TableDiv[Division]=G2) ) * TableDiv[Yearly Salary] > G3), 2, 4), 2, -1)

💡 Pay special attention to the set of parentheses that surround the two [Division] tests.  This is necessary to ensure that these tests occur independently of the [Yearly Salary] test.

When the formula gets this long, it’s easy to lose focus on what is happening.

Here’s the formula with some strategic line feeds included to help break the logic into its constituent pieces.

What to Learn Next

You now know how to filter with AND logic, OR logic, same-column criteria, and how to return and sort specific columns. Here are some natural next steps:

• If you need to count or sum matching rows instead of extracting them, see our guides on COUNTIF and SUMIF with partial text match.
• If your FILTER formulas are getting long and repetitive, the LET function lets you assign names to expressions so you only write them once.
• To limit user input in your criteria cells, use drop-down lists. Consistent values prevent typos that silently break FILTER results.
• And if you want to go deeper with dynamic array functions like FILTER, SORT, UNIQUE, and XLOOKUP, check out our New Excel Functions course.

Download the Workbook

Featured Bundle

Power Excel Bundle

10x your productivity in Excel 💪 by mastering Excel’s Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.

Learn More

LinkedIn Twitter YouTube

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.

More About Leila Join 500,000+ professionals in our courses