Sum top n values in a range

LARGE-and-SEQUENCE-functions-vertical-array

To sum the largest n values in a range, you can use a formula that combines the SUM, TAKE, and SORT functions.

How to sum the largest numbers in an Excel range?

To sum the n largest numbers, follow the steps below:

  1. Open Excel
  2. Type =SUM(TAKE(SORT(B3:B12,, -1), n))
  3. Press Enter
  4. 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:

  1. Range: B3:B12 refers to the range of numbers from which we want to find the top N values.
  2. SORT function: This function sorts the values in the specified range.
  3. Direction -1: The -1 tells Excel to sort the numbers in descending order (largest to smallest).
  4. 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.
  5. SUM function: The SUM function sums the values returned by the TAKE function.
sum take sort example

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.

sumproduct-large-top-n-values-to-sum

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}

LARGE-return-a-horizontaly-array

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, 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)))

LARGE-and-SEQUENCE-functions-vertical-array

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.

SUM-LARGE-SEQUENCE