How to FILTER by multiple criteria (AND / OR)

FILTER rows that satisfy several conditions — multiplied for AND, added for OR.

The formula

How it works

Each parenthesized condition is an array of TRUE/FALSE; multiplying them gives 1 only where BOTH are true (150 and 200 are East and above 100 — the East 80 fails). For OR, add instead: (A2:A6="East")+(B2:B6>250) keeps rows passing either test, and the + can't double-count because FILTER only asks whether the result is nonzero. This condition algebra is the same trick SUMPRODUCT uses — learn it once, use it in both.

Verified, not just documented

We ran =FILTER(B2:B6,(A2:A6="East")*(B2:B6>100)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 150, 200 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 150, 200 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

FILTER — see full Excel, Google Sheets & LibreOffice compatibility for each.

Related recipes

• How to average a range that contains errors
• How to count unique values that match a condition
• How to find values that appear in both of two lists
• How to use the FILTER function
• How to calculate a median with a condition (MEDIANIF)

Related comparisons

• ARRAYFORMULA vs dynamic arrays: the same idea, three dialects
• FILTER vs QUERY: Sheets' two ways to slice data
• FILTER vs VLOOKUP: return every match, not just the first