Sum highest n numbers based on conditions

{ 4 Comments }

By Ashish Mathur · · , · Leave a comment

Assume a two column database with names in column A and numbers in column B.  Names in column A may be repeated.  If a user types a certain name in a cell, a formula should sum the highest three values from column B for that name.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the PowerPivot add-in, then a simple DAX formula can solve this problem.

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system’s performance.

You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • I have a spreadsheet where column A I have a list of divisions and in column B I have a list of numbers.

    – I need to look for the largest 4 numbers in the list.
    – Only 2 numbers can come from the same divisions
    – If there are 3 numbers from the same division I need to find the highest 2 of those 3 not just the first 2 that appear.

    Reply

    • Hi,

      Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

      Solution for MS Excel 2010 and higher versions

      If you are using the PowerPivot add-in, then a simple DAX formula can solve this problem. You may refer to the third worksheet of this workbook.

      Solution for all versions of MS Excel

      While this solution works for all versions of MS Excel, it uses a Data Table (which in turn uses array formulas – an array formula (Ctrl+Shift+Enter)). Array formulas, if used extensively in the workbook, adversely effect the system’s performance.

      You may refer to my solution this workbook.

      Hope this helps. Let me know how this works for you.

      Reply

      • Ashish,

        Thank you for your help. I have a couple of questions:

        1) I’m not sure where the number in G1 comes from or its purpose.

        2) In step 2, you said to create a one-way Data Table as shown in C:14-F15, I’m not sure how to do that or what that means

        3) I will need re-create this several times below this one as there will be many
        countries with teams of up to 8 skiers that I will need to do these calculations for

        Thanks!
        Marc

        Reply

        • Hi,

          1. The number is cell G1 is required for creating the one-way Data Table. You may hide that column if you wish to. Infact you can even delete that number. On deleting that number, though you will see errors in cell C15 and range G4:G11, the results in range D15:F15 (cells which really matter to you) will show you the correct result

          2. A data table is a dynamic What-If Analysis tool. A Data Table has been used here to minimize the use of spare columns. If I do not use a Data Table here, I will have to use three spare columns (as against the one used now) will have to used – one each for Slalom, Tricks and Jump. Here goes the explanation:

          a. In range D14:F14, I entered the =COLUMN() formula. This will generate 4,5 and 6 respectively (If you wish, you may manually type 4,5, and 6 in D14:F14 – the result will not be affected) – these are the column positions of Slalom, Tricks and Jump. To make the result more meaningful, I custom formatted D14:F14 (Ctrl+1 > Number > Category > Custom > Type) to show Slalom, Tricks and Jump.
          b. When you create a one way data table, one by one each of the values in D14:F14 will go into cell G1. This is because when creating the Data Table, I specified the row input variable as cell G1. Furthermore, since G1 also feeds into the formula in cell C15 (custom formatted to show Top 4 Scores), the 4 highest values in the Slalom, tricks and Jump column are shown in range D15:F15 respectively
          c. So when G1 becomes 4, then computations in range G4:G11 will happen with respect to the numbers in the Slalom column and four highest values from the Slalom column (with a maximum of 2 from each category) will be shown in cell D15. Likewise, when G1 becomes 5, then computations in range G4:G11 will happen with respect to the numbers in the Tricks column and four highest values from the Tricks column (with a maximum of 2 from each category) will be shown in cell E15
          d. To create a one way Data Table, I selected C14:F15 and went to Data > Table. In the row label, I selected cell G1 and left the column label empty. Click on OK.

          3. I do not understand your third question.

          Reply