I'm currently upgrading my sheets for an MTB program. I'm changing all my formulas to arrayformula so as to avoid anyone being left out when I forgot to drag the formula.

All that's left is the formula in cell P3. The formula is to sum the best 3 times for both categories of M and E.

Here's the link to the spreadsheet.

Any help is highly appreciated.

I want to sum the best 3 times for both categories of M and E.

CodePudding user response：

Use MAP, like this:

={"BEST";ARRAYFORMULA(IF(E3:INDEX(E:E,MAX(ROW(E:E)*(E:E<>"")))=E2:INDEX(E:E,MAX(ROW(E:E)*(E:E<>""))-1),,MAP(E3:E,LAMBDA(team,IF(COUNT(FILTER(K:K,E:E=team,D:D="M"))<3,"DNQ",IF(COUNT(FILTER(K:K,E:E=team,D:D="E"))<3,"DNQ",IF(COUNT(FILTER(K:K,E:E=team))<6,"DNQ",SMALL(FILTER(K:K,E:E=team,D:D="M"),3) SMALL(FILTER(K:K,E:E=team,D:D="M"),2) MIN(FILTER(K:K,E:E=team,D:D="M")) SMALL(FILTER(K:K,E:E=team,D:D="E"),3) SMALL(FILTER(K:K,E:E=team,D:D="E"),2) MIN(FILTER(K:K,E:E=team,D:D="E")))))))))}

I entered this formula in cell Q2.