Dynamic Arrays & Filtering
Filter Data Between Two Dates (Inclusive Boolean Multiplication & Fallback)
Extract rows whose date serials fall inclusively between Start_Date and End_Date using FILTER boolean multiplication (Date >= Start) * (Date <= End) with swap-safe MIN/MAX bounds.
Why FILTER requires boolean multiplication * instead of AND(...) across date columns
Inside FILTER(array, include, [if_empty]), wrapping two range comparisons in AND(B2:B6 >= E2, B2:B6 <= E3) collapses the entire array to a single scalar TRUE or FALSE, causing FILTER to fail with #VALUE! because include no longer matches the row height of array.
Multiplying elementwise comparisons—(B2:B6 >= MIN(E2, E3)) * (B2:B6 <= MAX(E2, E3))—produces a 1/0 column vector of identical height, includes exact start-day and end-day boundary records, automatically handles accidentally swapped start/end inputs via MIN and MAX, and returns a clean "NO_ROWS" message when no dates qualify.
| Window Requirement | FILTER Include Expression | Boundary Behavior |
|---|---|---|
| Inclusive [Start, End] | (B2:B6 >= E2) * (B2:B6 <= E3) | Includes Start and End dates |
| Strictly Exclusive (Start, End) | (B2:B6 > E2) * (B2:B6 < E3) | Excludes boundary dates |
| Timestamp-Safe End Day | (INT(B2:B6) >= E2) * (INT(B2:B6) <= E3) | Captures 14:30 timestamps on End_Date |
Swap-Safe Inclusive Date-Window FILTER Formula
Normalize lo_dt = MIN(E2, E3) and hi_dt = MAX(E2, E3) inside LET and pass "NO_ROWS" as the 3rd argument of FILTER.
=LET(lo_dt, MIN(E2, E3), hi_dt, MAX(E2, E3), FILTER(A2:A6, (B2:B6 >= lo_dt) * (B2:B6 <= hi_dt), "NO_ROWS"))Companion Window Count & Revenue Tie-Out
Cross-check your dynamic filtered slice with COUNTIFS and SUMIFS over the same [lo_dt, hi_dt] boundaries.
=SUMIFS(C2:C6, B2:B6, ">=" & MIN(E2, E3), B2:B6, "<=" & MAX(E2, E3))Try the example
Use the original values or change them to test your understanding.