Summary

To sum the largest n values in a range, you can use a formula based on the LARGE function and the SUMPRODUCT function. In the generic form of the formula above, range contains numeric values and n is the number of values to sum. In the example shown, the formula in cell E5 is:

=SUMPRODUCT(LARGE(data,{1,2,3}))

where data is the named range B5:B16. The result is the sum of the largest 3 values (70, 65 55) which is 45.

Generic formula

=SUMPRODUCT(LARGE(range,{1,2,n}))

Explanation 

In this example, the goal is to sum the largest n values in a set of data, where n is a variable that can be easily changed. At a high level, the solution breaks down into two steps (1) extract the n largest values from the data set and (2) sum the extracted values. This problem can be solved with the LARGE function together with the SUMPRODUCT function, as explained below. For convenience only, the range B5:B16 is named "data".

LARGE function

The LARGE function is designed to return the nth largest value in a range. For example:

=LARGE(range,1) // 1st largest
=LARGE(range,2) // 2nd largest
=LARGE(range,3) // 3rd largest

Normally, LARGE returns just one value. However, if you supply an array constant (e.g. a constant in the form {1,2,3}) to LARGE as the second argument, k , LARGE will return an array of results instead of a single result. For example:

=LARGE(A1:A10,{1,2,3})

will return the 1st, 2nd, and 3rd largest values in the range A1:A10.

Example

In the example shown, the formula in E5 is:

=SUMPRODUCT(LARGE(data,{1,2,3}))

Working from the inside out, the LARGE function is configured to return the 3 largest values in the range B5:B16:

=LARGE(data,{1,2,3}) // returns {70,65,55}

Because we provide three separate values for k, the result is an array that contains three results:

{70,65,55}

This array is returned directly to the SUMPRODUCT function:

SUMPRODUCT({70,65,55}) // returns 190

With just a single array to process, SUMPRODUCT sums the values in the array and returns 190 as a final result.

SUM alternative

It is common to use SUMPRODUCT in problems like this because SUMPRODUCT can handle arrays natively without any special handling in Legacy Excel. However, in a modern version of Excel, you can use the SUM function instead:

=SUM(LARGE(data,{1,2,3})) // returns 190

Note: this is an array formula and must be entered with Control + Shift + Enter in Legacy Excel.

When n becomes large

As n becomes a larger number, it becomes tedious to enter longer array constants like {1,2,3,4,5,6,7,8,9,10}, etc. In this situation, you can use a formula snippet to create sequential numbers automatically based on the ROW and INDIRECT functions. For example, to sum the largest 10 values in a range, you can use a formula like this:

=SUMPRODUCT(LARGE(range,ROW(INDIRECT("1:10"))))

Here, the INDIRECT function converts the text string "1:10" to the range 1:10, which is returned to the ROW function. The ROW function then returns the 10 row numbers that correspond to the range 1:10 in an array like this:

{1;2;3;4;5;6;7;8;9;10}

Note this is actually a vertical array, as indicated by the semicolons (;) but the LARGE function will happily accept a vertical or horizontal array as the k argument. Once INDIRECT and ROW have been evaluated, the formula is in the same form as before:

=SUMPRODUCT(LARGE(range,{1;2;3;4;5;6;7;8;9;10}) // sum 10 largest

LARGE will return the 10 largest values, and SUMPRODUCT will return the sum of these values as a final result.

Variable n

To set up a formula where n is a variable in another cell, you can concatenate inside INDIRECT. For example, if A1 contains n, you can use:

=SUMPRODUCT(LARGE(range,ROW(INDIRECT("1:"&A1))))

This allows a user to change the value of n directly on the worksheet and the formula will respond instantly.

With the SEQUENCE function

New in Excel 365, the SEQUENCE function can generate numeric arrays directly in one step, which eliminates the need for the ROW + INDIRECT snippet above, or even a hardcoded array constant. With SEQUENCE, we can write a formula like this:

=SUM(LARGE(range,SEQUENCE(3)) // sum largest 3 values
=SUM(LARGE(range,SEQUENCE(9)) // sum largest 9 values

Note: Because SEQUENCE requires the new dynamic array engine in Excel (where array behavior is native), we have also replaced SUMPRODUCT with the SUM function. Read more about SUMPRODUCT and arrays here.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.