FILTER with two conditions

How it works, step by step

1. 01(B2:B1000="West") checks each Region and returns an array of TRUE and FALSE values, one per row.
2. 02(C2:C1000="Open") does the same for Status.
3. 03Multiplying the two arrays turns TRUE into 1 and FALSE into 0, so a row scores 1 only when both conditions are true. This is how FILTER does AND.
4. 04FILTER returns the rows of A2:D1000 that scored 1 and spills them down and across from the formula cell.
5. 05"No matches" appears instead of a #CALC! error when no row qualifies.

The same logic in four tools

=FILTER(A2:D1000,(B2:B1000="West")*(C2:C1000="Open"),"No matches")

=IFERROR(FILTER(A2:D1000,B2:B1000="West",C2:C1000="Open"),"No matches")

SELECT order_id, region, status, amount
FROM orders
WHERE region = 'West'
  AND status = 'Open';

import pandas as pd

open_west = orders[(orders["region"] == "West") & (orders["status"] == "Open")]

Mistakes that break it

• !FILTER needs Excel 365 or Excel 2021. Excel 2019 and older return #NAME?.
• !Google Sheets FILTER has no if_empty argument. It takes each condition as its own argument, and you wrap it in IFERROR to show a message when nothing matches.
• !The results spill into the cells below and to the right. Anything already in that area causes #SPILL! in Excel or #REF! in Google Sheets until those cells are cleared.
• !For OR logic, add the conditions instead of multiplying: (B2:B1000="West")+(B2:B1000="East"). In pandas, use | and keep each comparison in parentheses.

Common questions

How do I use FILTER with multiple criteria?

Multiply the conditions for AND, as in (B2:B1000="West")*(C2:C1000="Open"), and add them for OR. In Google Sheets you can also pass each AND condition as a separate argument.

Why does FILTER return #CALC!?

No rows met the conditions and no if_empty value was given. Add a third argument such as "No matches" to show text instead.

What is the SQL equivalent of FILTER?

A SELECT with a WHERE clause. Each FILTER condition becomes a WHERE condition joined with AND or OR.