Sum the Top N Values

Excel Formulas › Sum

All versionsLARGEArray

To total just the biggest few values in a list — top 3 sales, three best months — pair LARGE with SUM. LARGE pulls the nth-largest value, and an array of positions grabs several at once.

Quick formula: to add the three largest numbers in B2:B8:
=SUMPRODUCT(LARGE(B2:B8, {1,2,3}))
{1,2,3} asks LARGE for the 1st, 2nd and 3rd largest; SUMPRODUCT adds them without needing Ctrl+Shift+Enter.

Functions used (tap for the full reference guide):

LARGESUMPRODUCTSUM

The example

Monthly sales. We want the total of the top 3.

AB
1MonthSales
2Jan$120
3Feb$300
4Mar$90
5Apr$250
6May$200
7Jun$60
8Top 3 total:$750

The formula

The top-3 total:

=SUMPRODUCT(LARGE(B2:B7, {1,2,3})) // 300 + 250 + 200 = 750

How it works

Two ideas combine:

  1. LARGE(B2:B7, 1) is the largest value, LARGE(..., 2) the second largest, and so on.
  2. Feeding LARGE an array of ranks, {1,2,3}, returns all three at once: {300, 250, 200}.
  3. SUMPRODUCT adds that little array — 750 — and, unlike plain SUM, it handles the array without Ctrl+Shift+Enter.
  4. Change {1,2,3} to {1,2,3,4,5} for the top 5, or point it at a cell with SEQUENCE(C1).

Try it: interactive demo

Live demo

Choose how many of the top values to add.

Total:   values:

Variations

Top N with a condition

Sum the top 3 West-region values — FILTER the list first (Excel 365):

=SUM(LARGE(FILTER(B2:B7, A2:A7="West"), {1,2,3}))

Bottom N (smallest)

Swap LARGE for SMALL:

=SUMPRODUCT(SMALL(B2:B7, {1,2,3}))

Make N a cell

Drive the count from a cell with SEQUENCE (Excel 365):

=SUM(LARGE(B2:B7, SEQUENCE(E1)))

Pitfalls & errors

Use SUMPRODUCT or array-enter. Plain =SUM(LARGE(B2:B7,{1,2,3})) needs Ctrl+Shift+Enter in older Excel. SUMPRODUCT avoids that.

#NUM! when N is too big. Asking for the 7th largest of 6 values errors. Keep N no larger than the count, or cap it with MIN(N, COUNT(range)).

Ties count as separate ranks. Two values of 300 occupy ranks 1 and 2, so both are included — usually what you want, but worth knowing.

Practice workbook

📊
Download the free Sum the Top N Values practice workbook
The sales list with a live top-N total, the bottom-N and conditional versions, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sum the top N values in Excel?
Use =SUMPRODUCT(LARGE(range, {1,2,3})) for the top 3. The array {1,2,3} tells LARGE to return the three largest values, and SUMPRODUCT adds them without Ctrl+Shift+Enter.
How do I sum the top N with a condition?
In Excel 365, filter first: =SUM(LARGE(FILTER(values, criteria), {1,2,3})). This restricts the pool before taking the largest values.
How do I sum the smallest N values?
Replace LARGE with SMALL: =SUMPRODUCT(SMALL(range, {1,2,3})) adds the three smallest values.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Sum by month · SUMIFS with multiple criteria · Rank values (no gaps)

Function references: LARGE · SUMPRODUCT · SUM