How to Sum the Top 5 (or Top N) Values in Excel

To sum the top 5 values in Excel, combine two functions: LARGE finds the five largest numbers and SUM adds them, as in =SUM(LARGE(C2:C9,{1,2,3,4,5})). It works in every version of Excel and handles ties correctly. This guide shows the formula, how to make "5" a number you can change, how to add only one group's top values, and how to avoid the errors people hit in reports and job tests.

The quick formula to sum the top 5 values#

Nest LARGE inside SUM and give LARGE a list of positions. All the examples use this sales table in A1:C9:

In an empty cell, type:

=SUM(LARGE(C2:C9,{1,2,3,4,5}))

The result is 24,050: 6,100 + 5,100 + 4,750 + 4,200 + 3,900. You don't need to sort the data first, and you don't need Ctrl+Shift+Enter, even in older Excel.

For the top 3, shorten the list: =SUM(LARGE(C2:C9,{1,2,3})) returns 15,950.

How the formula works#

LARGE returns the k-th largest number in a range. Its syntax is =LARGE(array, k). On its own, =LARGE(C2:C9,1) returns 6,100 and =LARGE(C2:C9,2) returns 5,100. The guide to LARGE and SMALL for top N values covers the function in detail.

The curly brackets {1,2,3,4,5} are an array constant: a fixed list of values typed straight into the formula. When you give LARGE a list for k, it returns a list of answers:

SUM then adds that list, the same way it adds a range. If SUM is new to you, the guide to the SUM function and AutoSum starts from the basics, and the SUM function guide is a quick reference.

You can also write it the long way, which some people find easier to read:

=LARGE(C2:C9,1)+LARGE(C2:C9,2)+LARGE(C2:C9,3)+LARGE(C2:C9,4)+LARGE(C2:C9,5)

It gives the same 24,050, but it gets long fast for a top 10.

How to sum the top N values with N in a cell#

Make N a cell when you want to switch between top 3, top 5 and top 10 without editing the formula. Type the number in F1, then use SEQUENCE to build the list of positions:

=SUM(LARGE(C2:C9,SEQUENCE(F1)))

With 5 in F1, the result is 24,050. Change F1 to 3 and it becomes 15,950. SEQUENCE(5) returns the list 1, 2, 3, 4, 5, so it replaces the typed {1,2,3,4,5}. SEQUENCE needs Excel 2021 or Microsoft 365.

In Excel 2019 and older, build the list with ROW and INDIRECT, and use SUMPRODUCT so you don't need Ctrl+Shift+Enter:

=SUMPRODUCT(LARGE(C2:C9,ROW(INDIRECT("1:"&F1))))

ROW(INDIRECT("1:5")) returns the row numbers 1 to 5, which act as the positions. SUMPRODUCT adds the results; see how SUMPRODUCT works and the SUMPRODUCT function guide.

In Microsoft 365 or Excel 2024 you can also sort and take the first N: =SUM(TAKE(SORT(C2:C9,,-1),F1)). It returns the same total.

How to sum the bottom 5 values#

Swap LARGE for SMALL. SMALL returns the k-th smallest number, so this adds the three lowest sales:

=SUM(SMALL(C2:C9,{1,2,3}))

The result is 9,750: 2,800 + 3,300 + 3,650. Bottom-N sums are useful for finding the weakest products or the cheapest quotes.

How to sum the top values for one group#

Filter the numbers first, then take the largest. To add the two best North sales in Excel 2021 or Microsoft 365:

=SUM(LARGE(FILTER(C2:C9,B2:B9="North"),{1,2}))

FILTER keeps only the North rows (4,200, 5,100, 4,750 and 6,100), and LARGE picks the top two. The result is 11,200 (6,100 + 5,100). Change "North" to "South" and you get 7,550 (3,900 + 3,650).

In older Excel, multiply by the condition instead:

=SUMPRODUCT(LARGE((B2:B9="North")*C2:C9,{1,2}))

(B2:B9="North") is TRUE or FALSE for each row. Multiplying turns South sales into 0 and keeps North sales as they are, so LARGE only sees North values at the top. This only works when the numbers are positive and the group has at least N values.

What happens with ties?#

LARGE counts each tied value as its own position, so SUM(LARGE(...)) always adds exactly N numbers. If two reps both sold 3,900, LARGE(C2:C9,5) and LARGE(C2:C9,6) would both return 3,900, and the top 5 would include just one of them.

You may see a shorter shortcut online:

=SUMIF(C2:C9,">="&LARGE(C2:C9,5))

It adds every value at or above the 5th largest. On the table above it also returns 24,050. With a tie at the cut-off, though, it adds all tied values, so you could get six or seven numbers in a "top 5". Use it only when you want ties included on purpose.

If you need to see which rows are in the top 5, not just the total, build a list with the top N list formula, or colour the rows with top 10 conditional formatting.

Why your top N sum is wrong or shows #NUM!#

Most problems come from the data or the size of N:

1. #NUM! error. LARGE fails when k is bigger than the number of values. A top 10 from 8 sales returns #NUM!. Cap N with MIN: =SUM(LARGE(C2:C9,SEQUENCE(MIN(F1,COUNT(C2:C9))))).
2. Blank cells and text. LARGE ignores them, which is usually what you want. A blank is never treated as 0.
3. Numbers stored as text. Imported figures that are really text are skipped, so the total looks too low. Convert them with VALUE or Text to Columns.
4. Semicolons in some regions. In countries that use a comma as the decimal sign, Excel uses semicolons between arguments and a different separator inside array constants. If {1,2,3} gives an error, use SEQUENCE(3) or the long LARGE+LARGE form.
5. Wrong range. A range that stops one row short quietly drops a value. Check that the range covers every row.

Practise it#

LARGE is a favourite in Excel interviews, because "what is the second-highest value?" and "add the top 3" test whether you know more than SUM. Practise LARGE and SMALL in a live spreadsheet: you type the formula and it checks your answer instantly. If you want a refresher on totals first, practise SUM and AutoSum. Then try the exercise on this page and add up the top 3 sales.