Have you ever needed to Sum the top N values in Excel? Maybe you need to find out how much the total of the top 5 sales were for the year. Or perhaps if you’re like me and you would like to know how many page views your top 5 posts get. Whatever the case, we’ll quickly go over how to use the Sum function to add up the top 5 values in your list.
Setting up the Data
Let’s take a simple worksheet like the following:
You can also copy and paste this data into your Excel workbook to follow along:
|Movie||Date Opened||Total Gross|
|Batman v Superman: Dawn of Justice||3/25/16||$330,360,194|
|The Jungle Book (2016)||4/15/16||$364,001,123|
|Captain America: Civil War||5/6/16||$408,084,349|
|The Secret Life of Pets||7/8/16||$368,384,330|
|Rogue One: A Star Wars Story||12/16/16||$532,177,324|
Now we’d like to find the top grossing films of 2016 and then sum them up to give us a grand total. How do we go about doing that?
Getting the Top 5 Values
First, we need to get the top 5 values from the list. We can do this by using the
LARGE() function in Excel. If you’re not familiar with the
LARGE() function, click here to lear more.
Normally, you would use the
LARGE() function to return a single value, but today, we need it to return multiple values. To do this, select 5 cells and then in the formula bar type the following formula:
Ctrl+Shift+Enter to create an array formula. The formula should look like this in the end:
Since you had 5 cells selected, the values will be spread across the 5 cells from top grossing film to least grossing film.
Here are the results of that formula:
OK, we have the top 5 grossing films, now all that’s left to do is sum up those values.
Putting it All Together
In a single cell, you can take that
LARGE() function we wrote earlier and wrap it with the
Be sure to enter this formula with
And with that, you end up with the top 5 grossing films: