Extract rows that meet several conditions with FILTER. Multiply the conditions for AND, add them for OR — the spilling result updates as the data changes.
The example
Filtering on two conditions at once.
| A | B | |
|---|---|---|
| 1 | Logic | Operator |
| 2 | AND | (c1)*(c2) |
| 3 | OR | (c1)+(c2) |
The formula
Multiply for AND, add for OR:
How it works
Boolean arithmetic drives the conditions:
- Each condition is an array of TRUE/FALSE (treated as 1/0).
- Multiplying them is AND — a row passes only if every condition is 1.
- Adding them is OR — a row passes if any condition is ≥ 1.
- Wrap a third FILTER argument to handle no matches:
FILTER(data, cond, "none")avoids a#CALC!error.
Mix AND and OR carefully. Group with parentheses: (A)*((B)+(C)) means “A and (B or C).” Boolean arithmetic has no operator precedence safety net here — parenthesize every group.
Try it: interactive demo
Region + min sales; AND or OR.
Variations
OR conditions
Add the arrays:
Handle no match
Default text:
AND with OR group
Parenthesize:
Pitfalls & errors
No match = #CALC! Supply the third argument (a default) so an empty result doesn’t error.
Use * and +, not AND()/OR(). The AND/OR functions collapse arrays to one value; multiply/add keeps them element-wise.
Equal-length conditions. Every condition array must match the data’s row count.
Practice workbook
Frequently asked questions
How do I use multiple conditions in FILTER?
Why does FILTER return #CALC!?
Why not use AND() and OR() inside FILTER?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class