Math & Trig
Sum top N (or bottom N) values with criteria: FILTER + SORT + TAKE
Total the top K largest deals inside a specific region—even when that region has fewer than K rows—by chaining FILTER, SORT, and TAKE.
Sum up to N matching values
Summing LARGE(FILTER(Deals, Region="West"), {1,2,3}) throws #NUM! the moment "West" has only 1 or 2 deals in the quarter!
By contrast, TAKE(array, 3) gracefully returns ALL available rows when an array has fewer than 3 items—and wrapping FILTER(Deals, Region="West", 0) ensures even a 0-match region returns 0 without error!
| Region (A) | DealAmount (B) | East Rank | Included in East Top 2? |
|---|---|---|---|
| East | 200 | 3rd | No |
| West | 900 | (West) | No (only 1 West deal) |
| East | 500 | 1st | Yes |
| East | 100 | 4th | No |
| East | 400 | 2nd | Yes (Top 2 = 500 + 400 = 900) |
Short-group-safe conditional Top N and Bottom N sum formulas
SORT(..., 1, -1) orders filtered values largest-to-smallest for Top N; SORT(..., 1, 1) orders smallest-to-largest for Bottom N.
=SUM(TAKE(SORT(FILTER(B2:B6, A2:A6="East", 0), 1, -1), 2))Conditional Bottom N sum (lowest 2 deals in East)
Changes SORT sort_order from -1 (descending) to 1 (ascending).
=SUM(TAKE(SORT(FILTER(B2:B6, A2:A6="East", 0), 1, 1), 2))Inputs and interpretation
Use a positive integer N and numeric values for eligible records. TAKE returns up to N rows, not all values tied at the cutoff. The zero FILTER fallback is used only when there are no matches; it does not add a zero to a nonempty negative-valued group.
Try the example
Use the original values or change them to test your understanding.