This guide will explain how to find the sum of the largest numbers in a given range in Excel.
We’ll use several Excel functions together to determine the summation of the largest values in a given cell range.
Let’s take a look at a simple example of where we might need to find the total sum of the top N values in a data set.
Suppose you have a list of donations for a local charity drive. You want to find out how much of the donated amount comes from the top five donors.
We can actually use the Excel functions LARGE
and SUMPRODUCT
to create a custom formula to achieve this.
The LARGE
Excel function will help us find the largest n numbers in a given data set. The SUMPRODUCT
Excel function will allow us to sum up the output of our LARGE
function.
Now that we know when to use the LARGE
and SUMPRODUCT
Excel functions, let’s see how this works on an actual sample spreadsheet.
A Real Example of Finding the Sum of the Largest N Numbers in Excel
The following section provides several examples of sheets that find the sum of the largest N numbers in Excel. We will also go into detail about the formulas and tools used in these examples.
First, let’s take a look at a spreadsheet with some sample data.
The table below shows a list of donors for a charity drive. The table also indicates how much each donor contributed to the cause.
We want to know how much money was donated by the five biggest donors.
To get the value in D2, we just need to use the following formula:
=SUMPRODUCT(LARGE(B2:B26;SEQUENCE(5)))
Let’s try to understand how the formula above works. First, we’ll use the SUMPRODUCT
function to add a range of values. We’ve chosen the SUMPRODUCT
function since it accepts an array of values as input.
To get our top 5 values, we use the LARGE
and SEQUENCE
functions together. The LARGE
function allows you to get the Nth largest value in a certain range. We must provide a cell range with numerical values and an integer N.
Instead of a single integer N, we can provide an array of integers to get multiple results. This feature will enable us to get the 1st largest value, 2nd largest value, and so on. The SEQUENCE
function allows us to create an array of incrementing integers of length N.
In the table below, we’ve created another table to illustrate the value of our summation given different values for N.
To get the values in Column E, we’ll use the following formula:
=SUMPRODUCT(LARGE($B$2:$B$26;SEQUENCE(D5)))
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
Use our sample spreadsheet to take a closer look at the formulas we used to get the sum of the largest N values in a range.
If you’re ready to try finding the sum of the largest N numbers, head over to the next section to read our step-by-step breakdown on how to do it!
How to Find the Sum of Largest N Numbers in Excel
This section will guide you through each step needed to find the sum of the largest N numbers in Excel. You’ll learn how to use the SUMPRODUCT
, LARGE
, and SEQUENCE
functions together to sum up the largest numbers in a range.
For this guide, we will continue using the donor dataset shown in the prior section.
Follow these steps to start finding the sum of the largest N numbers in Excel:
- First, select the cell that will hold the final result. In this example, we’ll use cell D2.
- Type ‘=SUMPRODUCT(‘ to start the
SUMPRODUCT
function.
- Type ‘LARGE(‘ to start the
LARGE
function. For the first argument, select the range that contains the list of values you want to get the top N numbers from. In this example, we’ll use the range B2:B26.
- In this example, we want to return the sum of the top five donors. If we want the
LARGE
function to return these five numbers, we must provide an array such as {1,2,3,4,5}. We can either type the array manually or with theSEQUENCE
function.
- Hit the Enter key to evaluate the function. In our example, we find out that the top five donors contributed a total of $7983.00 to charity.
- In the example below, we can see that we achieve the same result if we use the
SEQUENCE
function instead of using a hard-coded array.
These are all the steps you need to find the sum of the largest N numbers in a given cell range.
This step-by-step guide is a quick introduction to using Excel functions to find the sum of the largest N numbers in a range.
Finding the sum of the largest N numbers is just one example of the many Excel tasks you can perform in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!