Lookup & Dynamic Arrays
FILTER Rows Matching This OR That Criteria with Boolean Addition (+)
Extract rows matching Condition A OR Condition B inside FILTER using elementwise boolean addition (condA) + (condB) > 0 instead of scalar OR().
Why OR() fails inside FILTER and why (cond1) + (cond2) > 0 works
Writing FILTER(A2:D7, OR(B2:B7 = "Security", C2:C7 = "P1")) fails with #VALUE! because OR() aggregates an entire array down to a single scalar TRUE/FALSE rather than returning a row-by-row boolean vector!
Adding two parenthesized array comparisons — (B2:B7 = B9) + (C2:C7 = B10) — produces 0 (neither), 1 (one condition met), or 2 (both met) for each row. Comparing > 0 (or passing the non-zero sum directly to FILTER) extracts every row matching either condition without duplicating rows where both are true.
| Incident | Team | Severity | (Team="Security") + (Sev="P1") | Included by FILTER? |
|---|---|---|---|---|
| INC-401 | Security | P2 | 1 + 0 = 1 | Yes (1) |
| INC-402 | Billing | P3 | 0 + 0 = 0 | No |
| INC-403 | Platform | P1 | 0 + 1 = 1 | Yes (2) |
| INC-404 | Security | P1 | 1 + 1 = 2 | Yes (3 — once!) |
| INC-405 | Analytics | P2 | 0 + 0 = 0 | No |
Comma-Joined Incident IDs Matching Team OR Severity
Extracts all incident IDs in A2:A6 where Team equals B8 OR Severity equals B9.
=TEXTJOIN(", ", TRUE, FILTER(A2:A6, (B2:B6 = B8) + (C2:C6 = B9) > 0, "No Matching Incidents"))Count of Filtered OR-Matched Rows
Counts rows matching either condition without double-counting dual-match rows like INC-404.
=SUM(--((B2:B6 = B8) + (C2:C6 = B9) > 0))Try the example
Use the original values or change them to test your understanding.