FILTER function with two criteria

Abstract

In this video, well set up theFILTER functionwith 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.

I’ll start off by placing the cursor in F5 and entering an expression to test for Texas.

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.

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.

Again, I’ll enclose this in parentheses.

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

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, thearrayis the source data we are filtering.

Theincludeargument 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.

When I press enter, FILTER extracts matching orders.

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