FILTER function with two criteria

In this video, we’ll set up the FILTER function with two criteria.

The FILTER function is designed to extract data from a list or table using supplied criteria. 

In this worksheet, we have  data that contains an order number, amount, name, and state.

Our goal is to use the FILTER function to extract orders from Texas with an amount of at least $100.

To explain how this logic works, I'm going to test the criteria first in a helper column, then copy it into the FILTER function.

I'll start off by placing the cursor in F5 and entering an expression to test for Texas. This will be:

=E5:E15="tx"

When I enter this expression as a formula, the results spill unto the worksheet alongside the data.

Notice we have a TRUE wherever the state is "tx" and a FALSE for other states.

Next let's extend the expression to also test for orders greater than or equal to 100.

Because we want an AND relationship - the orders must be from Texas AND be at least 100 - we want to use multiplication.

So, I'll wrap the original expression in parentheses, then add an asterisk (*).

Now I need another expression to test for amounts greater than or equal to 100.

C5:C15>=100

Again, I'll enclose this in parentheses.

=(E5:E15="tx")*(C5:C15>=100)

When I press enter, the math operation converts the TRUE and FALSE values to 1s and 0s.

As you can see, the 1s correspond to orders where the state is "tx" and the amount is at least 100.

This tells us the criteria logic is working properly.

OK, so now let's set up the FILTER function.

To save typing, I'll first copy the formula in the helper column to the clipboard.

In the FILTER function, the array is the source data we are filtering.

The include argument is the criteria we just tested, so I'll paste that here from the clipboard. 

Notice I'm not including the equals sign from the original formula.

For "if_empty" I'll use "No data" in double quotes.

=FILTER(B5:E15,(E5:E15="tx")*(C5:C15>=100),"no data")

When I press enter, FILTER extracts matching orders.

If I temporarily change an amount to $100 for a Texas order, we'll see a new record appear in the results.

Now that we know FILTER is working properly, I can delete the test formula in the helper column.