Math & Trig
Sum top N or bottom N values (overall and with criteria)
Total the N largest deals or N lowest vendor quotes dynamically using SUM, TAKE, SORT, and FILTER.
Combine SORT and TAKE for any dynamic N
Hardcoding LARGE(B2:B6, 1) + LARGE(B2:B6, 2) + LARGE(B2:B6, 3) cannot adapt when a user changes N from 3 to 5 in a control cell.
SORT(B2:B6, 1, -1) orders numbers largest-to-smallest, TAKE(..., N) keeps the top N rows, and SUM totals them cleanly. Switching sort_order from -1 to 1 sums the bottom N smallest values.
| Region (A) | DealAmt (B) | Rank |
|---|---|---|
| East | 500 | 1st largest |
| West | 150 | Smallest |
| East | 350 | 2nd largest |
| West | 300 | 3rd largest |
| East | 200 | 2nd smallest |
Top N, bottom N, and conditional top N formulas
Insert FILTER(B2:B6, A2:A6 = "East", 0) inside SORT to sum the top N values within a single region or product line.
=SUM(TAKE(SORT(B2:B6, 1, -1), C2))Sum bottom N smallest values
Bottom 2 values are 150 + 200 = 350.
=SUM(TAKE(SORT(B2:B6, 1, 1), C2))Inputs and interpretation
Use a positive integer N and numeric, nonblank amounts. Duplicate values occupy separate positions. If N exceeds available rows, TAKE returns all rows. The conditional FILTER fallback chooses a zero total for an absent group; it is not a real zero-valued transaction. Filter or validate mixed text/blank imports before sorting.
Try the example
Use the original values or change them to test your understanding.