Google Spreadsheet: sum of top 3 highest values if they meet a certain condition

Question

My problem is as follows: competitors are competing for a specific team with two different exercises. Team consists of 3 to 5 members. Team's exercise 1 is computed as a sum of three best exercises done by team members and the same for team's exercise 2. Team total score is the sum of team's exercise 1 and team's exercise 2.

If in column A are names of competitors, in column B exercise number (1 or 2), in column C team name, column D score, then:

I know how to get the names of the teams: =UNIQUE(C$2:C). Let's say we put this in column E.

I know how to get the sum of three highest values (not for a specific team): ArrayFormula(SUM(LARGE(D2:D,{1,2,3}))))

I know how to get sum of all values for a specific team: SUMIF(C$2:C,E2,D$2:D)

But I don't know how to put both together to get a sum of three highest values for a specific team. That would be my I2 and J2 values in the below formula (+rule is that there have to be 3-5 people to form the team, otherwise that team does not have a team score):

=IF(COUNTIF(FILTER(C$2:C,ISEVEN(ROW(C$2:C))),E2)>2,I2,0)

=IF(COUNTIF(FILTER(C$2:C,ISODD(ROW(C$2:C))),E2)>2,J2,0)

In my spreadsheet: https://docs.google.com/spreadsheets/d/1cteuRg6tgt5uPq5u4a3MWwRt7lp-7Gq0LBzL5WGNCZg/edit?usp=sharing

I would expect the team's score for first exercise to be what's in column I and for the second what is column J.

Answer 1

You can use QUERY() function with LIMIT. Try-

=SUM(QUERY(A2:D,"select D where B=1 and C='" & E2 & "' order by D DESC limit 3"))

You can also use FILTER() function then QUERY() to get top 3 and SUM().

=SUM(QUERY(SORT(FILTER(D2:D,C2:C=E2,B2:B=1),1,0), "limit 3"))

To spill results dynamically (no need manual drag down formula), use MAP() or BYROW() function.

=MAP(E2:INDEX(E2:E,COUNTA(E2:E)),
LAMBDA(x,
SUM(QUERY(SORT(FILTER(D2:D,C2:C=x,B2:B=1),1,0), "limit 3"))
))