How to Sum Top n Values in Excel (3 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

When working with numbers and large datasets in Excel, sometimes we need to find the top numbers from the dataset and sum the values. The sales type dataset needs this most frequently. In this article, I will show you 3 suitable ways to sum any top n values from a data set in Microsoft Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


3 Methods to Sum Top n Values in Excel

First of all, let us have a look at our data set. We have a sales record of two months for a company named Nicholas Group.

Now we will try to sum any top n values from the sales.

First, let’s try to sum up the top 5 sales from January.

Sample Dataset to Sum Top n Values in Excel


1. Combine LARGE Function with SUMIF to Sum Top n Values in Excel

We can accomplish the targetted task by using the SUMIF function of Excel along with the LARGE function. Follow the steps below to do this.

📌 Steps:

  • First, click on cell F5.
  • Afterward, insert the following formula and press the Enter key.
=SUMIF(C5:C17,">="&LARGE(C5:C17,5))

Combine the LARGE Function with SUMIF Function to Sum Top n Values in Excel

🔎 Formula Breakdown:

  • LARGE(C5:C17,5)

It returns the 5th largest value from the cells C5 to C17.
Result: $25000.00

  • SUMIF(C5:C17,”>=”&LARGE(C5:C17,5))

It returns the sum of the cells from C5 to C17 which contain values greater than or equal to the previous result.
Result: 150,000.00

Thus, you will get the sum of the top 5 sales from January, which is $150,000.00.

Read More: Excel Sum If a Cell Contains Criteria (5 Examples)


2. Use SUM Formulas to Sum Top n Values in Excel

We can also calculate the sum of the top 5 sales for the month of January using the SUM function of Excel.

2.1 Combine SUM, IF, and RANK Functions to Sum First n Numbers

You can execute the task using the SUM, IF, and RANK functions of Excel. Follow the steps below to do this.

📌 Steps:

  • First and foremost, click on cell F5 and insert the following formula.
=SUM(IF(RANK(C5:C17,C5:C17)<=5,C5:C17,0))

Combining SUM, IF, and RANK Functions to Sum Top n Values in Excel

🔎 Formula Breakdown:

  • IF(RANK(C5:C17,C5:C17)<=5,C5:C17,0)

It takes an array of criteria (RANK(C5:C17,C5:C17)<=5) in place of a single criterion, and it returns a TRUE for each cell between C5 to C17, if it has a value in the top 5 zones. Otherwise, it returns a FALSE. When TRUE, it would return the corresponding cell value from C5 to C17, and when FALSE, it would return 0.
Result: {0,0,0,0,0,25000,28000,30000,0,0,0,35000,32000}

  • SUM(IF(RANK(C5:C17,C5:C17)<=5,C5:C17,0))

It sums up the values of the resultant array.
Result: $150,000.00

  • Subsequently, press the Ctrl + Shift + Enter key, as this is an array formula.

As a result, we will get the same result as earlier, $150,000.00

Read More: How to Add Multiple Cells in Excel (6 Methods)


2.2 Combine SUM with LARGE Function

You can calculate the sum of the top 5 sales using the SUM function with the LARGE function of Excel. Go through the steps below to do this.

📌 Steps:

  • At the very beginning, click on cell F5.
  • Subsequently, insert the following formula and press Ctrl + Shift + Enter key.
=SUM(LARGE(C5:C17,{1,2,3,4,5}))

Combining SUM and LARGE Functions

🔎 Formula Breakdown:

  • LARGE(C5:C17,{1,2,3,4,5})

It takes an array of values {1,2,3,4,5} in place of a single value k. And returns an array containing the 1st, 2nd, 3rd, 4th, and 5th largest values from the C5:C17 range.
Result: {35000,32000,30000,28000,25000}

  • SUM(LARGE(C5:C17,{1,2,3,4,5}))

It sums up the previous resultant values.
Result: $150,000.00

Consequently, we will get the sum of the top 5 sales from January, which is  $150,000.00.


2.3 Combine SUM with SEQUENCE Function

Now, think for a moment. You have to calculate the sum of the top 100 values from a data set. Is it possible to enter manually 1,2,3… up to 100 in the previous formula? That is almost impossible. So what is the way?

No worries. We can still accomplish it using the SUM and the LARGE function of Excel, but this time using one more function, SEQUENCE. Let’s try to find out the sum of the top 10 sales from January this time. Follow the steps below to achieve this target.

📌 Steps:

  • First, click on cell F5.
  • Following, insert the formula below and hit the Enter key.
=SUM(LARGE(C5:C17,SEQUENCE(10,1)))

Using the SEQUENCE Function inside SUM Function

🔎 Formula Breakdown:

  • SEQUENCE(10,1)

It returns an array of values from 1 to 10.
Result: {1,2,3,4,5,6,7,8,9,10}.

  • LARGE(C5:C17,SEQUENCE(10,1))

It returns the top 10 large sales in the range of C5 to C17.
Result:
{35000,32000,30000,28000,25000,24500,24000,23000,22000,21000}

  • SUM(LARGE(C5:C17,SEQUENCE(10,1)))

It sums the previous resultant array.
Result: $264,500.00

Thus, we will get the sum of the top 10 sales of January, which is $264,500.00.

Note:

The SEQUENCE function is only available in Office 365.

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)


Similar Readings


3. Use SUMPRODUCT Formulas to Sum Top n Values in Excel

You can also use the SUMPRODUCT function to sum top n values in Excel by following any of the options below.

3.1 SUMPRODUCT with LARGE, ROW, and INDIRECT Functions

In Option 2 from section 2, we saw how you can calculate the sum of the top 5 sales using the LARGE and the SUM functions. It is easy for the top 5, but when it comes to large numbers like the top 50 or the top 100, this process is quite unuseful.

We achieved the same goal in option 3 of section 2 for top 10 sales, using a combination of SUM, LARGE, and SEQUENCE functions. But the SEQUENCE function is only available in Office 365.

Now, we will again achieve the same goal, but this time using a combination of SUMPRODUCT, LARGE, ROW, and INDIRECT functions. Follow the steps below to accomplish this.

📌 Steps:

  • Initially, click on cell F5.
  • Subsequently, insert the formula below and press the Enter key.
=SUMPRODUCT(LARGE(C5:C17,ROW(INDIRECT("1:10"))))

Using SUMPRODUCT, LARGE, ROW, and INDIRECT Functions to Sum Top n Values in Excel

🔎 Formula Breakdown:

  • ROW(INDIRECT(“1:10”))

It returns an array of values from 1 to 10.
Result: {1,2,3,4,5,6,7,8,9,10}.

  • LARGE(C5:C17,ROW(INDIRECT(“1:10”)))

It returns the top 10 large values in range C5 to C17.
Result: {35000,32000,30000,28000,25000,24500,24000,23000,22000,21000}

  • SUMPRODUCT(LARGE(C5:C17,ROW(INDIRECT(“1:10”))))

It returns the sum of the top 10 large values.
Result: $264,500.00

Thus, we will get the same result for the top 10 sales of January as earlier, $264,500.00.

Read More: How to Add Rows in Excel with Formula (5 ways)


3.2 SUMPRODUCT with RANK Function

Let’s try to find out the sum of the top 10 sales again, this time using SUMPRODUCT along with the RANK function of Excel. Follow the steps below to do this.

📌 Steps:

  • First and foremost, click on cell F5 and insert the following formula.
=SUMPRODUCT(C5:C17,--(RANK(C5:C17,C5:C17)<=10))
  • Subsequently, press the Enter key.

Using SUMPRODUCT Function with RANK Function

🔎 Formula Breakdown:

  • –(RANK(C5:C17,C5:C17)<=10)

It returns an array of TRUE or FALSE. For each cell in the range C5 to C17 which falls under the top 10 it returns a TRUE, and FALSE for the rest.‘–‘ converts the TRUE and FALSE array into an array of 1 and 0.
Result: {0,0,1,1,0,1,1,1,1,1,1,1,1}

  • SUMPRODUCT(C5:C17,–(RANK(C5:C17,C5:C17)<=10))

It multiplies C5:C17 cell values to the previous resultant array. Therefore, it returns the sum of the top 10 sales.
Result: $264,500.00.

As a result, we will get the sum of the top 10 sales values from January.

Read More: Excel Sum Last 5 Values in Row (Formula + VBA Code)


How to Sum Top n Values in Excel with Criteria

Let’s try something different now. Try to find out the sum of any top n values, but with a criterion. Let’s try to calculate the sum of the top 10 sales in January, but we will consider only the sales which are less than $30,000.00.

You can do this using the SUMPRODUCT function just like in the previous example. Follow the steps below to achieve the result.

📌 Steps:

  • At the very beginning, click on cell F5.
  • Following, insert the formula below.
=SUMPRODUCT(C5:C17,(--(RANK(C5:C17,C5:C17)<=10))*(--(C5:C17<30000)))
  • Subsequently, hit the Enter key.

Sum Top n Values in Excel with Criteria

Thus, you will get the sum of the top 10 sales in January less than $30,000.00 is $167,500.00 as your desired result.

Note: 

Here C5:C17 is the range of values from which I extracted the top 10, you use your one.

And if you want, you can also use the more complicated formula below:

=SUMPRODUCT(((LARGE(C4:C18,ROW(INDIRECT("1:10"))))<30000)*LARGE(C4:C18,ROW(INDIRECT("1:10"))))

Read More: Sum Cells in Excel: Continuous, Random, With Criteria, etc.


How to Sum Top n Values in Excel with Texts Inside

Now, come to February. Let’s try to find out the top 10  sales from February. Obviously, we can use any formula from the earlier sections. But one problem is that, in a few cells in the February column, there is a text “No Sales”. And when we use the formulas, it will show errors. So, what to do?

But, no worries. Just wrap the LARGE portion from any of the above formulas within an IFERROR function. To make it clear, follow the steps below to achieve your desired target in this way.

📌 Steps:

  • First and foremost, click on cell F5 and insert the following formula.
=SUM(IFERROR(LARGE(D5:D17,ROW(INDIRECT("1:10"))),0))
  • Subsequently, press the Ctrl + Shift + Enter key.

Sum Top n Values in Excel with Text Inside

Thus, the error because of text calculation will be ignored and you will get the top 10 sales from February would be $261,000.00.

Note:

You can use any other formula from section 2, but just wrap the LARGE portion within an IFERROR function.

Read More: How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)


Conclusion

So, in this article, I have shown you 3 suitable ways to sum top n values in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo