3 Quick Approaches to Sum Top N Values in Excel

Sometimes we need to calculate the sum top N values in Excel. In this article, we are going to show you how to calculate the sum of top N values in Excel with 3 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.


📂  Download Excel File

Download the free workbook from here:


Learn to Sum Top N Values in Excel with 3 Approaches

While writing this article, “Sum Top N Values in Excel”, we’ve considered a dataset that contains approximately 4 columns and 12 rows. In this dataset, we added Name, Marks in Physics, and Marks in Maths to make you better understand. But if you want, you can change the entities and put the values of your own.

dataset to calculate sum top N values in Excel

APPROACH

1. Using LARGE Function and SUMIF Function Together

Using the  LARGE function and  SUMIF function together is one of the easiest methods to calculate the sum of top N values in Excel. You just need to follow the below-mentioned steps to obtain this process.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, select cell F6.
  • Now write the given formula on that cell.

=SUMIF(C6:C12,”>=”&LARGE(C6:C12,3))

🔨 Formula Breakdown 

Here

👉  SUMIF(C6:C12) calculates the sum of the values of cells C6 to C12.

👉  LARGE(C6:C12,3) finds the top 3 values from cells C6 to C12.

  • Now tap Enter.
  • Finally, you will get the desired result.

result of SUMIF and LARGE functions to calculate sum top N values in Excel

Consequently, using the LARGE function and SUMIF function together is a very easy way to calculate the sum top N values in Excel.

APPROACH

2. Applying SUMPRODUCT Function

There is another way to calculate the sum of top N values in Excel. We know this as the SUMPRODUCT formulas. There are several procedures to obtain these formulas.

SUMPRODUCT FUNCTION

2.1 SUMPRODUCT and RANK Functions

If you want to apply the SUMPRODUCT formulas in an easy way then you can use SUMPRODUCT and RANK functions together. You just have to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, click on cell F6.
  • Secondly, write the formula down on that cell.

=SUMPRODUCT(C6:C12,–(RANKC6:C12,C6:C12)<=3))

🔨 Formula Breakdown

Here

👉  –(RANKC6:C12,C6:C12)<=3) implies a set of TRUE or FALSE. For every cell in the C6 to C12 which ranks the top 3, it gives a TRUE, and FALSE for the remaining.‘–‘ refers to the TRUE and FALSE into an array of 1 and 0.

👉  SUMPRODUCT(C6:C12) gives the sum values of the set.

  • Now press Enter.
  • Lastly, you will get the required values.

result of SUMPRODUCT and RANK function to calculate sum top N values in Excel

Finally, it is a very user-friendly technique to calculate sum top N values in Excel.

SUMPRODUCT FUNCTION

2.2 Adopting SUMPRODUCT, LARGE, ROW, and INDIRECT Functions Combined

Using SUMPRDUCT, LARGE, ROW, and INDIRECT functions combinedly to calculate sum top N values in Excel is a very simple method. You can do it by maintaining the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell F6.
  • Secondly, write the given formula on that cell.

=SUMPRODUCT(LARGE(C6:C12,ROW(INDIRECT(“1:5”))))

🔨 Formula Breakdown

Here

👉  LARGE(C6:C12,ROW(INDIRECT(“1:5”))) refers top 5 values from cells C6 to C12.

👉  SUMPRODUCT(LARGE(C6:C12,ROW(INDIRECT(“1:5”)))) gives the sum of top 5 values from cells C6 to C12.

  • Now tap Enter.
  • Consequently, you will get the desired values as well.

result of SUMPRODUCT, LARGE, ROW and INDIRECT functions

Undoubtedly, this is a very simple formula in Excel.

📕 Read More: 5 Ways to SUM Last 5 Values in Row in Excel

APPROACH

3. Application of SUM with Other Functions

Another approach to calculating sum top N values in Excel is applying the SUM formulas. There are several methods to obtain these formulas.

SUM WITH OTHER FUNCTIONS

3.1 SUM, IF and RANK Functions

Adopting SUM, IF, and RANK functions to calculate the sum top N values in Excel is a very simple method. You can do it by obtaining the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, click on cell F6.
  • Secondly, write the following formula there.

=SUM(IF(RANK(D6:D12,D6:D12)<=3,D6:D12,0))

🔨 Formula Breakdown

Here,

👉  IF(RANK(D6:D12,D6:D12)<=3,D6:D12,0) refers TRUE for every cell from D6 to D12, when it gives a value in the top 3 ranks. Otherwise, it gives a FALSE. If TRUE, it will give the value from D6 to D12, and if FALSE, it will give 0.

👉  SUM(IF(RANK(D6:D12,D6:D12)<=3,D6:D12,0)) implies the sum of the top 3 values from cells D6 to D12.

  • Now press Enter.
  • Finally, you will find the desired values.

result of SUM, IF and RANK functions

Eventually, this is a very handy formula in Excel.

SUM WITH OTHER FUNCTIONS

3.2 SUM and LARGE Functions

Applying SUM and LARGE functions together to calculate the sum top N values in Excel is a very easy method. You can do it by maintaining the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, select cell F6.
  • Secondly, write down the following formula there.

=SUM(LARGE(D6:D12,{1,2,3,4,5}))

🔨 Formula Breakdown

Here,

👉  LARGE(D6:D12,{1,2,3,4,5}) refers the top 1st, 2nd, 3rd, 4th, and 5th values from cells D6 to D12.

👉  SUM(LARGE(D6:D12,{1,2,3,4,5})) gives us the sum of top 1st, 2nd, 3rd, 4th, and 5th values from cells D6 to D12.

 
  • Now tap Enter.
  • Consequently, you will get the required values as well.

using SUM and LARGE functions

Truly, this is a very user-friendly formula in Excel.

SUM WITH OTHER FUNCTIONS

3.3 SUM and SEQUENCE Functions

Using SUM, and SEQUENCE functions together to calculate the sum top N values in Excel is a very simple approach. You can do it by performing the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select cell F6.
  • Secondly, write down the following formula on that cell.

=SUM(LARGE(D6:D12,SEQUENCE(6,1)))

🔨 Formula Breakdown

Here,

👉  LARGE(D6:D12,SEQUENCE(6,1)) gives the top 6 values sequentially (1,2,3,4,5,6) from cells D6 to D12.

👉  SUM(LARGE(D6:D12,SEQUENCE(6,1))) implies the sum of the top 6 values sequentially from cells D6 to D12.

  • Thirdly tap Enter.
  • Lastly, you will see the desired values as well.

Use of SEQUENCE function

Consequently, this is a very simple formula in Excel.


How to Calculate Sum of Top N Values with Criteria in Excel

You can also calculate the sum of top N values with criteria in Excel. It is not a very difficult task. You can easily do it if you follow the below-mentioned steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, select cell F6.
  • Secondly, write the following formula on that particular cell.

=SUMPRODUCT(D6:D12,(–(RANK(D6:D12,D6:D12)<=5))*(–(D6:D12<76)))

🔨 Formula Breakdown

Here

👉  RANK(D6:D12,D6:D12)<=5))*(–(D6:D12<76) gives the top 5 values from cells D6 to D12 whose values are greater than 76.

👉  SUMPRODUCT(D6:D12,(–(RANK(D6:D12,D6:D12)<=5))*(–(D6:D12<76))) implies the sum fo those values.

  • Thirdly press Enter.
  • Later on, you will see the required values as well.

using SUMPRODUCT and RANK functions

Truly, this is a very useful method to calculate the sum of top N values with criteria in Excel.

📕 Read More: How to Add Specific Cells in Excel (5 Easy Ways)


How to Find Top N Values with Criteria in Excel

If you want to find top N values with criteria in Excel, then this part is for you. You can find them by maintaining the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell F6.
  • Secondly, write the following formula on that cell.

=LARGE(IF(D6:D12>60,C6:C12,””),ROW(A1:A5))

🔨 Formula Breakdown

Here

👉  ROW(A1:A5) implies the top 5 values.

👉  LARGE(IF(D6:D12>60,C6:C12,””),ROW(A1:A5)) refers top 5 values from cells D6 to D12 whose values are greater than 60.

  • Now press Enter.
  • Lastly, you will get the required values.

using LARGE and IF functions to calculate top N values with criteria in Excel

Consequently, this is a very handy method to calculate top N values with criteria in Excel.

📕 Read More: 5 Effective Ways to Sum Random Cells in Excel


📄 Important Notes

While performing the processes mentioned above, you need to be aware of these things:

🖊️  You should be careful while using the Excel functions.

🖊️  You must choose the cell carefully.


📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌  Firstly, we used the LARGE Function and SUMIF Function together in Excel.

📌  Secondly, we applied the SUMPRODUCT formulas.

📌  Thirdly, we adopted SUM formulas to calculate the sum top N values in Excel.

📌  Fourthly, we calculated the sum top N values with criteria in Excel.

📌  Finally, we tried to show you how to find top N values with criteria in Excel.


Conclusion

We wish that you would be able to calculate sum of top N values in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.


Related Articles

(Visited 81 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo