Dynamic Arrays & Lookup
Sum the Bottom N Values Matching Criteria (FILTER, SORT & TAKE Pipeline)
Total the N lowest vendor quotes, fastest lap times, or smallest Regional invoices within a specific category using SUM(TAKE(SORT(FILTER(...)), N)) with graceful IFERROR handling.
Why FILTER + SORT + TAKE replaces legacy Ctrl+Shift+Enter SMALL arrays
Summing the lowest N numbers for a single category previously required nested SUMPRODUCT(SMALL(IF(cat=target, vals), ROW(INDIRECT("1:" & N))))—which broke with #NUM! whenever a category had fewer than N matching rows!
Chaining FILTER -> SORT -> TAKE(sorted, N) -> SUM is both readable and resilient: TAKE(array, N) automatically returns all available rows when a category has fewer than N items (e.g., 2 Customs quotes when N = 3) instead of throwing #NUM!, and wrapping IFERROR(..., 0) handles zero-match categories cleanly.
| Category Query | Matching Quotes | Sorted Ascending | Bottom N = 2 Sum | Bottom N = 3 Sum |
|---|---|---|---|---|
| Freight | 480, 320, 590, 410 | 320, 410, 480, 590 | 730 | 1,210 |
| Customs (Only 2 rows) | 290, 210 | 210, 290 | 500 | 500 (Safe clamp!) |
| Air (0 rows) | (None) | #CALC! -> IFERROR 0 | 0 | 0 |
Sum Lowest N Category Values (Fewer-Than-N Safe)
Filters C2:C7 where B2:B7 equals E2, sorts ascending, takes up to F2 lowest values, and sums them (returning 0 if no rows match E2).
=IFERROR(SUM(TAKE(SORT(FILTER(C2:C7, B2:B7 = E2)), F2)), 0)Average Lowest N Category Values
Replacing SUM with AVERAGE computes the mean of the N most competitive bids in category E2.
=IFERROR(AVERAGE(TAKE(SORT(FILTER(C2:C7, B2:B7 = E2)), F2)), 0)Try the example
Use the original values or change them to test your understanding.