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.
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.
Consequently, using the LARGE function and SUMIF function together is a very easy way to calculate the sum top N values in Excel.
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.
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.
Finally, it is a very user-friendly technique to calculate sum top N values in Excel.
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.
Undoubtedly, this is a very simple formula in Excel.
📕 Read More: 5 Ways to SUM Last 5 Values in Row in Excel
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.
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.
Eventually, this is a very handy formula in Excel.
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.
Truly, this is a very user-friendly formula in Excel.
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.
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.
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.
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.