To sum the largest n values in a range, you can use a formula that combines the SUM, TAKE, and SORT functions.
How to find the sum of the largest N values in Excel?
Steps to sum top n values in a range in Excel:
- Open Excel
- Type =SUM(TAKE(SORT(B3:B12,, -1), n))
- Press Enter
- The formula returns the sum of the largest n values.
Example
We want to sum the largest five values using an unsorted list in the example.
Formula:
SORT(B3:B12, , -1)
Let’s break down the formula step-by-step:
- Range: B3:B12 refers to the range of numbers from which we want to find the top N values.
- SORT function: This function sorts the values in the specified range.
- Direction -1: The -1 tells Excel to sort the numbers in descending order (largest to smallest).
- TAKE function: This function takes the first N rows from a range. Here, 5 is used as the second argument, taking the first 5 values from the sorted list.
- SUM function: The SUM function sums the values returned by the TAKE function.
In this case, it will sum the top 5 values: 100 + 90 + 80 + 70 + 60 = 400
Using the LARGE and SUMPRODUCT functions
Without user-defined functions, you can use the LARGE and SUMPRODUCT functions to summarize the top N largest numbers in a range.
Explanation: By default, the LARGE function returns a single value. To create a return array, use the {1, 2, 3, 4, 5} constant when looking up the top 5 largest values.
Formula:
=SUMPRODUCT(LARGE(numbers,{1,2,3,4,5}))
As usual, evaluate the formula from the inside out:
=LARGE(numbers, {1, 2, 3, 4, 5}
The result is a horizontal array that contains the top 5 largest values. The SUMPRODUCT function will use the result array as an argument:
=SUMPRODUCT(100, 90, 80, 70, 60) =400
SUM the largest N numbers using the SEQUENCE function
With Microsoft 365 (Excel 365), you can use the SEQUENCE function to generate an array using a single step and replace the SUMPRODUCT function with a simple SUM.
Formula:
=SUM(LARGE(numbers,SEQUENCE(n)))
To summarize the five largest values in a range, use the formula below:
=SUM(LARGE(numbers,SEQUENCE(5)))
The formula returns a vertical array containing the largest n values, in this case, n=5. Use the SUM function to put the result into a single cell.