Pick top 3 values and sum them

Question

Let's say I have some data that looks like this:

PLAYER    |Team      |Points
Smith     |Suns      |25
Jones     |Suns      |15
Martin    |Suns      |23
Chen      |Suns      |3
Williams  |Suns      |17
Quill     |Marvel    |40
Banner    |Marvel    |1
Stark     |Marvel    |1
Odinson   |Marvel    |1
Parker    |Marvel    |3
Curly     |Spurs     |2
Franke    |Spurs     |5
Wayne     |Spurs     |23
Weasley   |Wizards   |21
Potter    |Wizards   |19
Granger   |Wizards   |15
Thompson  |Bobcats   |12
Boehme    |Bobcats   |13

If I wanted to write a formula to sum the top 3 scores from each team, how would I do that?

For example, it would return this data:

Team   |Points
Suns   |65
Marvel |44
Spurs  |30
Wizards|55
Bobcats|25

I've considered using a filter to say "if the value is greater than the middle value of the members of the team" or something, but if there were less than 3 members that wouldn't work.

I've also considered sumif but I don't think that would be appropriate for the same reason.

I'm also looking to find the sum of the MIDDLE 3 scores, as well, but I'm sure if I get advice on finding the top 3, I can figure out how to adapt it for the middle 3.

Is this something that Power Query could be helpful with? I've dabbled, but I'm not a pro. (If I absolutely had to, I could also probably whip up something in VBA, but I'd rather not go down that route if possible, since I want to put it in a Google Sheet to share it on the web eventually).

Answer 1

Sort your data by team and score descending. Then use this formula that finds the first of each team and sums the next three scores or the number of scores for that team if less:

=SUM(INDEX(C:C,MATCH(F2,B:B,0)):INDEX(C:C,MATCH(F2,B:B,0)+MIN(COUNTIF(B:B,F2)-1,2)))

Answer 2

This formula will sum the top 3 scores for each team:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E2,C$2:C$19),{1,2,3}),""))

It's an array formula, so it must be entered with CTRLShiftEnter, rather than just Enter.

The results are shown below:

How it works: The IF() returns a list of the points for the team listed in column E. Then LARGE() takes just the top 3 scores. IFERROR() handles the cases where there are less than 3 scores. Finally, SUM() adds them up.

EDIT: To get the sum of the middle 3 scores, I tried to use the reference form of INDEX() with literal arrays and a formula to pick the "area_num":

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX(({1,2,3},{2,3,4}),,,INT(COUNTIF(B2:B19,E11)/2))),""))

But it wouldn't accept the array constants as the reference. I finally got it to work by using a helper column to specify the arrays:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX((H$1:H$3,H$2:H$4),,,INT(COUNTIF(B2:B19,E11)/2))),""))

Answer 3

My approach is only very slightly different to get the sum of the top 3 & middle 3 scores.

1. I sorted the source data on Team name as the primary field in ascending order and on Points as the secondary field in descending order. (To allow calculating the sum of the top 3 scores manually).
2. I've used a formula to generate the list of Teams in both ascending & descending order.
3. Finally, I calculated the sum of the top 3 and middle 3 scores, using a formula from one of the answers above.

Here are the formulas for:

Team list in ascending order:

{=INDEX($D$216:$D$233, MATCH(0, COUNTIF($J$215:J215, $D$216:$D$233), 0))}

Team list in descending order:

  =IFERROR(LOOKUP(2,1/(COUNTIF($G$215:G215,$D$216:$D$233)=0),$D$216:$D$233),"")

Sum of the top 3 Scores for teams in ascending order:

{=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{1,2,3}),0))}

Sum of the middle 3 Scores for teams in ascending order:

=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{2,3,4}),0))

See Note 2 below for an explanation of this formula.

Notes:

1. Replace J216 with G216 to get top & middle 3 scores for Teams in descending order.
2. Since the maximum count of Teams is 5, I have assumed that the middle 3 scores are numbers 2,3 & 4. This eliminates the highest score from the total, even if there are only 2 or 3 scores. If you believe a team with only 2 or 3 scores should have all scores contribute to the total, then consider one of the answers above.