10+ ways to SUM Top N values in data range – Ready to be Ben 10 of Excel?

8
15957

We all love to add things up. Its always good if things add up to the way we want. And no doubt from Excel newbie to pro everyone uses SUM formula. We have recently discussed how to do conditional SUM as well where we learnt to add up only those values that certain criteria.

However, we may wish to add up only the top 2 or 3 or 10 or any N values while doing calculations on simple data range to complex data using pivot tables. Following methods help you understand how different functions / formula in Excel help you get the total of top or first ‘N’ values in data set.

To best understand and apply the methods learnt in this tutorial please download this excel workbook and follow the instructions below to master each technique.

Again this is a fairly large post and with animations it grew even more. So it has been divided in four pages. Don’t forget to check out other methods on next pages.

Just to clear out confusion if there is any, the following methods are based on simple numbers but you can use the calculation on Dates as well with a little to no additional effort. So try out the methods on dates as well and tweak up the formula a little if needed.

  1. Method 1 + Method 2
  2. Method 3 + Method 4 + Method 5
  3. Method 6 + Method 7 + Method 8
  4. Method 9 and 10 + Special tip + Method 11

Method 1: Going to the top one LARGE step at a time

LARGE function let you find the nth largest value in the data set. Meaning it can find you the largest, second largest, third largest or 10th largest or whatever largest value you want, it is very easy.

Syntax of LARGE function is:

=LARGE(array,k)

Array is collection of item out of which you are trying to find out the nth largest value. This can be a simple data range or a named range.

Where K acts as a point of reference and is said as kth largest or nth largest value to be found. For example in a collection of three items 10, 11 and 12 the largest is 12 for which K=1. For second largest i.e. K=2 the value is 11.

Knowing this, if you want to know the sum of the top 3 values in a data range you can do so using following steps:

Step 1: Open the file and go to tab named:

Step 2: Observe the given information, your data is in  range. In cell put the following formula and press Enter key

=large(range,1)+large(range,2)+large(range,3)

Bonus Tip – Using named ranges

If your data is large then selecting the range every is a peril in the way of efficiency so select the range once and go to name box and type any name you want to give to data range. In our case our range is Sales column and we named it: numbers.

The other column of is Sales person. Select the data of that column as well and name it: salesperson. We are going to need that later.

This way formula writing will be much easier and saves you selection time and errors in selection. With name defined your formula will become:

=LARGE(numbers,1)+LARGE(numbers,2)+LARGE(numbers,3)

Method 2: Making large small – Butterflies all the way!

Well its quite manageable if you are summing up the top three values but what if you want to have a sum of top five values? You will have to write the formula whole five times. Now this is seriously going to enLARGE! So cut the crazy bit out by changing the formula to this:

=SUM(LARGE(numbers,{1,2,3}))

The above is an array formula we have mentioned how many top values to be summed in an array fashion (look closely we have curly brackets). That part of the formula i.e. LARGE function, finds the top three values from the data set. To confirm select the LARGE(numbers,{1,2,3}) part of the formula and press F9 key and you will get:

{299,298,297}

Press Ctrl+Z to have the LARGE function back in place and press Enter

— | Method 3 + Method 4 + Method 5

8 COMMENTS

  1. Good day
    i have a challenge . been working on a report card, but i can’t go through 🙁
    NAME ENG MATHS ADMA BIO GEO CIV PHY CHE AVG PSN
    BRIAN 55 3 66 2 63 2 72 2 55 3 71 2 83 1 80 1 427 68 8
    BKALUMO 65 2 52 3 0 FAIL 65 2 70 2 60 2 88 1 50 3 400 56 16
    IAN 70 2 24 FAIL 36 FAIL 50 3 58 3 71 2 75 1 18 FAIL 360 50 22
    TREVIS 88 1 54 3 51 3 80 1 70 2 77 1 90 1 72 2 477 73 3
    GRIFFINS 68 2 26 FAIL 51 3 46 4 59 3 77 1 70 2 60 2 385 57 1 HARRISON58 3 12 FAIL 34 FAIL 54 3 55 3 71 2 75 1 40 4 353 50 25
    ROSTEN 78 1 42 4 45 4 58 3 74 2 63 2 85 1 80 1 438 66 7
    VICTOR 78 1 56 3 60 2 56 3 59 3 83 1 85 1 24 FAIL 421 63 9
    GABRIEL 48 4 38 FAIL 33 FAIL 52 3 40 4 49 4 75 1 12 FAIL 302 43 28
    The card works like this . a teacher will enter the mark for a pupil then it will calculate the grade ie 1-9(fail). and also calculates the best 6 subjects including english as shown in the 3rd last column.(SUM(LARGE(E3:Q3,{1,2,3,4,5}))+C3)

    i want to now add a column that will calculate the points (sum of best grade). i have tried alot of formulars but the challenges comes because the grades where found by a formular and are not in a range (in different columns)
    =SUM(SMALL(range,{1,2,3,4,5}))+M34 – this formular isnt giving an answer but an error ### #num!. am stuck on how to go about it. i noticed it could be because the grades for a particular pupil are in different columns and are a result of a formular.

    Please help. Thanks in advance

  2. Hi Fazal,
    Good day…
    A very nice article, thanks for sharing.

    “For some reason SUMIF don’t work if we don’t put criteria in inverted commas. If I find the reason I will let you know. And someone out there know the reason please share in the comment section”

    I would like to add tgat excel help says:

    Criteria is the condition that must be met if a value is to be included in the sum. If the condition contains any text, logical or mathematical symbols it must be enclosed in double quotation marks. Quotation marks are not necessary if the criterion is just a number.

    Regards,
    Khalid

  3. Hi Hasaan,
    I have a question about how to take the next step in conditional summarising , I would like to summarise volume by highest Profit not by highest volume, to arrive at the last column here. The column was done manually.
    I’m trying to make sales fall into the profitability categories: 50 best transactions, next 25 best transactions etc.
    I will use three if statements to achieve the category name, but the summation of volumes based in highest profit eludes me. Do you have an idea? I can’t sort the data set because I have 4 quarters to do.

    The data set is very large, but this gives a good indication:

    Profit Vol Rank Cumulative sales by highest profit
    What Q1 Q1 Q1 Q1
    Goods 1 350000 53 1 0-50
    Goods 1 40000 7 4 75-100
    Goods 1 100000 17 2 50-75
    Goods 1 20000 3 8 75-100
    Goods 1 70000 5 3 50-75
    Goods 1 26000 4 5 75-100
    Goods 1 -10000 19 11 75-100
    Goods 1 20000 3 7 75-100
    Goods 1 22000 5 6 75-100
    Goods 1 5500 1 9 75-100
    Goods 1 -500 0 10 75-100

  4. Hi,

    I have a similar issue that I cant find a good solution too without creating new columns of data. I have two columns, A and B. I want to find the average of the top three values in column B given that the condition X in column A is met at the same time. I have tried all kinds of formulas but I can’t seem to get it right.

    Your help is very much appreciated!

  5. Hello, I wanted to do a Conditional TOP N Items Sum… I have a Column A where i have City Name (27 Cities), Column B Where an Establishment Name, Column C where Order Qty. Its a huge list of 32000 Establishment. Now i want to know the Order Qty of Each of the 27 City from the top 10 Ordering Establishments. How Can i do that. I have gone through this but could not make use of it.

    • Hey Ruchir – I had a similar problem, one workaround is to first use Sort the dataset by col A and then use Find & Replace (Ctrl+H).

      After sorting, add a Subtotal to your dataset through the Data tab on the Ribbon – could be anything, but let’s use COUNT for the example. At every change in City Name, put a count on Col C, i.e. Order Quantity.

      Now, find and replace all the = signs on the sheet with a blank. I will essentially use find and replace to alter the formula to give us the the results, so removing the blank prevents any errors through the process – we’ll bring it back in the end 🙂

      Now find and replace all the ) signs with ,{1,2,3,4,5,6,7,8,9,10})). After this, find and replace all the SUBTOTAL(3, with =SUM(LARGE(
      I think that should solve it 🙂

      Cheers,
      Vasudha

LEAVE A REPLY