How to FILTER by multiple criteria (AND / OR)
✓ Verified in LibreOffice 25.8.7.3FILTER rows that satisfy several conditions — multiplied for AND, added for OR.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =FILTER(B2:B6,(A2:A6="East")*(B2:B6>100)) | Multiply conditions for AND; add them (with >0) for OR. Excel 365/2021. |
| Google Sheets | =FILTER(B2:B6,(A2:A6="East")*(B2:B6>100)) | Sheets also accepts FILTER(B2:B6,A2:A6="East",B2:B6>100) — extra arguments AND together. |
| LibreOffice Calc | =FILTER(B2:B6,(A2:A6="East")*(B2:B6>100)) | Needs LibreOffice 24.8+. |
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. Every formula here is confirmed by actually executing it.