FILTER With Multiple Criteria: AND and OR

The FILTER function handles multiple criteria with simple maths: multiply conditions when all of them must be true, and add them when any one can be true. =FILTER(A2:D8,(B2:B8="East")*(C2:C8="Laptop")) returns East laptop sales only. This guide shows AND, OR, a mix of both, criteria from cells, and why the AND and OR functions don't work here.

How does FILTER combine multiple criteria?#

FILTER keeps the rows where its include argument is TRUE, or any number other than 0. Each condition, such as B2:B8="East", gives a list of TRUE and FALSE values, one per row. When you multiply or add those lists, Excel turns TRUE into 1 and FALSE into 0:

Multiplying gives 1 only when both are TRUE, which is AND. Adding gives at least 1 when either is TRUE, which is OR. That's the whole trick.

FILTER needs Excel 2021, Excel 2024 or Microsoft 365. Its results spill into the cells below and to the right, so leave them empty. If you're new to FILTER, start with the guide to the FILTER function.

All examples use this sales table in A1:D8:

How to filter with AND (all conditions true)#

Multiply the conditions, each in its own brackets. To return East rows that sold laptops:

=FILTER(A2:D8,(B2:B8="East")*(C2:C8="Laptop"))

The result is two rows: Ana and Fay. Cara is East but sold monitors, so she's dropped.

AND works well for number ranges too. To find sales between 500 and 1,000:

=FILTER(A2:D8,(D2:D8>=500)*(D2:D8<=1000))

This returns Dan (900), Fay (700) and Gus (650). You can multiply as many conditions as you need. Each one must be TRUE for the row to stay.

How to filter with OR (any condition true)#

Add the conditions with a plus sign. To return every row in the North or West region:

=FILTER(A2:D8,(B2:B8="North")+(B2:B8="West"))

The result is Ben, Dan, Eli and Gus. OR can also test different columns. =FILTER(A2:D8,(B2:B8="North")+(D2:D8>=1200)) keeps rows that are in the North or have sales of 1,200 or more: Ana, Dan, Eli and Gus.

When both conditions are true, the sum is 2. FILTER treats any non-zero number as TRUE, so the row appears once, not twice.

How to mix AND and OR in one FILTER#

Group the OR part in an extra set of brackets, then multiply it by the AND part. To find laptop sales in the East or West:

=FILTER(A2:D8,(C2:C8="Laptop")*((B2:B8="East")+(B2:B8="West")))

The result is Ana, Eli and Fay. Dan sold a laptop, but in the North, so he's left out.

The brackets work like they do in maths. Without the extra pair, Excel multiplies first and then adds, which gives "East laptops, plus everything in the West". That would wrongly include Ben's monitor sale. The guide to AND, OR and NOT in Excel explains this logic in normal formulas.

How to use criteria from cells#

Replace the typed values with cell references, so anyone can change the filter without editing the formula. If G2 holds a region and H2 a minimum sales amount:

=FILTER(A2:D8,(B2:B8=G2)*(D2:D8>=H2),"No matches")

With East in G2 and 1000 in H2, the result is Ana's row. Change G2 to West and you get Eli instead. A drop-down list in G2 turns this into a simple report tool.

The third argument, "No matches", is what FILTER shows when nothing passes. Without it, an empty result returns #CALC!.

For date conditions such as "this month" or "between two dates", the same multiply pattern applies. See how to filter by date. For partial text matches, like every product containing "lap", see filtering rows that contain text.

Why don't AND() and OR() work inside FILTER?#

The AND and OR functions return a single answer for everything you give them. AND(B2:B8="East",C2:C8="Laptop") checks all 14 values at once and returns one FALSE, because not every value is TRUE. FILTER needs one answer per row, so it returns an error instead of the rows you want.

Multiplication and addition work row by row, which is why they're used instead. Remember it as: * means AND, + means OR. This is the most common mistake people make with FILTER and multiple criteria.

Common problems with multiple criteria#

Most problems come from brackets, ranges or the spill area. Check these:

1. Missing brackets. B2:B8="East"*C2:C8="Laptop" is read in the wrong order. Wrap each test: (B2:B8="East")*(C2:C8="Laptop").
2. Ranges of different sizes. Every condition must cover the same rows as the array, or FILTER returns #VALUE!.
3. #SPILL! Something is in the way of the results. Clear the cells below and to the right.
4. #CALC! No rows matched. Add a third argument like "No matches".
5. Numbers stored as text. A test like D2:D8>=500 skips text numbers. Convert them first.

To add up the filtered rows instead of listing them, wrap FILTER in SUM; the guide to FILTER and SUM together shows how. To return several matches for one lookup value, see looking up multiple values.

Practise it#

FILTER with several conditions is a favourite in data analyst and advanced Excel tests. Practise the FILTER function in a live spreadsheet, then practise AND, OR and NOT to make the logic second nature. Each formula is checked instantly. The FILTER function guide is a quick syntax reference, and the data analyst Excel test lets you try FILTER under time pressure.