Excel Tip: SUM Top 5 values July 2nd, 2013

Vinod Kumar

Since Excel Tips have been most sought after posts from a productivity part, I thought let me introduce an interesting tip in this blog post. Have you ever had a requirement to get the “Average of Top 5” or “Sum of Top 5” values from a list? What is the most common method that you used? I have seen most people use the sort function to order the values in descending order and just select the range of values to find SUM, Average and so on. So this post is a twist to that requirement. You can also apply the tip to other functions like Average which makes this post even more interesting.

Our  Dataset 

The dataset is simple. Seen below is a list of some random Sales figures and our requirement is to get the SUM of Top 5 Sales figures in D3 cell.

To start with, let us first find our top Sales figure first. This can be done using the LARGE function. The second argument “k” is to give the offset. Value of 1 means we need the top sales number. This is shown below.

The output can be seen as below:

Duplicate LARGE Function

Now that we figured out how to get the 1st, 2nd, 3rd and so on. We can just duplicate the function on the same range and make our SUM. It is as simple as it gets.

As you can see this for some reasons doesn’t look right. We seem to be duplicating and if we again look at the LARGE function. It takes just a value and doesn’t take a range.

Using SUMPRODUCT

Since SUM is taking in only values and not arrays. We need to find a way to pass some sort of array. Here we have SUMPRODCT function that takes in a set of array to work with. And to take a range of values top 5 (1+2+3+4+5), I have created a temp range with these values.

Super cool – Isn’t it?

What will happen if we do the same with SUM function?

You can see that for some strange reason the top 1 value was returned and it didn’t do a SUM for the array.

Introducing CTRL+SHIFT+ENTER

Here is the magic shortcut (CTRL+SHIFT+ENTER) – Edit our cell from above “=SUM(LARGE(B3:B10, C2:C7))” and press our magic shortcut.

Now there is something interesting that happens. Now the SUM of values is done for an array of values. The CTRL+SHIFT+Enter has many interesting use. Here I have shown one of them.

Since the array is shown using the {} – this doesn’t work if add the parenthesis manually. A small extension to this formulae is to add the array of {1,2,3,4,5} manually and then hitting our magic key CTRL+SHIFT+Enter also can do the trick.

Hope you enjoyed this tip and do let me know if you have used this in the past anytime. I will try to bring some more use of arrays in future posts.

Tags: , , , , , , , , , ,

This entry was posted on Tuesday, July 2nd, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


7 Responses to “Excel Tip: SUM Top 5 values”

  1. New Learning for me. Thanks for sharing.

  2. Jonathan Weavers says:

    Hey –

    Your last post [Excel Tip: SUM Top 5 values] was freaking awesome. I have gone ahead and added your stuff to my Feedly account. Please keep me updated if you post anywhere else.

    Keep rocking –

    Jon

    • Vinod Kumar says:

      Thanks Jonathan for your kind words. This is my single window to blogs and I do write few guest blogs at sqlauthority.com from time to time. Glad to hear you liked it.

  3. Beena says:

    Learning of the day for me….thanks for sharing.

  4. avinash reddy says:

    wow wow wow very very nice and superb article done by you vinod sir glad to see one more tip from you today

Leave a Reply