Math & Trig
Sum first N (or last N) matching values by customer (TAKE + FILTER)
Sum the first or last N matching orders in a chronologically sorted transaction log using TAKE and FILTER.
Chronological First-N vs Value-Sorted Top-N
Sorting amounts descending selects the largest orders. Keeping the original chronological row order selects the earliest or latest orders instead. Sort the full transaction records by date before using these formulas.
Positive N selects the first matching rows; negative N selects the last. A first-N-order total is not the same as a first-90-day total, which requires a date condition.
Enter a positive whole number in E2. If fewer than N orders match, all available matches are included; no match returns zero.
| TxOrder | Account (A) | Amount (B) | Slice Role for "ACME" (N = 2) |
|---|---|---|---|
| 1 | ACME | $120 | 1st ACME order (First 2 = $270) |
| 2 | BETA | $400 | Skipped (Different account) |
| 3 | ACME | $150 | 2nd ACME order |
| 4 | ACME | $220 | 3rd ACME order (Last 2 = $520) |
| 5 | ACME | $300 | 4th ACME order (Latest) |
First N and Last N matching-order sum formulas
If an account has fewer than N orders (say only 1 order when N = 2), TAKE safely returns all available rows without throwing an error!
=IF(OR(E2<1,E2<>INT(E2)), "Enter a positive whole number", SUM(TAKE(FILTER(B2:B6, A2:A6 = D2, 0), E2)))Sum the LAST N matching entries for account D2
Negate -E2 inside TAKE to pull from the bottom of the filtered match array.
=IF(OR(E2<1,E2<>INT(E2)), "Enter a positive whole number", SUM(TAKE(FILTER(B2:B6, A2:A6 = D2, 0), -E2)))Try the example
Use the original values or change them to test your understanding.