Dynamic Arrays
FILTER with optional criteria: treat blank filter inputs as "Match All"
Build interactive multi-dropdown FILTER dashboards where clearing any search box includes all rows instead of returning zero matches.
Why plain (Region = E2) breaks as soon as a user clears E2
A plain equality criterion searches for blank records when its input is empty. For a dashboard where empty means all, add the empty-input test to each equality condition.
The >0 comparison makes each optional criterion a boolean mask; multiplying masks combines them with AND.
| Region Box (E2) | Status Box (F2) | Rows Returned |
|---|---|---|
| "North" | "Open" | Only North + Open rows (1 row) |
| (blank) | "Open" | All Open rows across every region (2 rows) |
| (blank) | (blank) | Entire unfiltered table (4 rows) |
Multi-field optional-criteria FILTER formula
Multiply each optional-input boolean expression so active filters combine with AND logic while blank inputs step aside.
=FILTER(A2:C5, (((E2="") + (A2:A5=E2)) > 0) * (((F2="") + (B2:B5=F2)) > 0), "None")List matching region names
TEXTJOIN combines matching names into one text cell; it does not count rows.
=TEXTJOIN(", ", TRUE, FILTER(A2:A5, (((E2="") + (A2:A5=E2)) > 0) * (((F2="") + (B2:B5=F2)) > 0), "None"))Additional worked calculation
Using the initial example data:
=SUM(FILTER(C2:C5, (((E2="") + (A2:A5=E2)) > 0) * (((F2="") + (B2:B5=F2)) > 0), 0))Inputs and interpretation
An empty filter input means no restriction, not search for blank records. A space is not empty. Comparisons here are case-insensitive. Keep all include ranges aligned with the source rows; errors in criteria require cleanup. The None fallback has one output cell, so ROWS alone cannot count zero matches.
Try the example
Use the original values or change them to test your understanding.