Forum Discussion
Formula worked in google sheets but now doesnt work in Microsoft 365
Below is what you want. I've used "IFERROR" for not enough game count.
=IFERROR(SUM(($A$3:$A$18=$C3)*$B$3:$B$18*(ROW($A$3:$A$18)<=SMALL(IF($A$3:$A$18=$C3,ROW($A$3:$A$18),""),COLUMN(C$1)))),"not enough")
To use Excel 365 to its full effect you would probably be best advised to forget about Google Sheets or legacy Excel. Modern Excel solutions need bear little resemblance to traditional spreadsheet working.
A formula to generate the result for a single team might be
= SUM(INDEX(FILTER(score,team=@distinctTeam),{1,2,3}))but, to create an array formula to analyse the data for an array of distinct teams, one could use MAP and a LAMBDA function along with a newly-released function TAKE that will select the first or last few members of an array
= MAP(UNIQUE(team),
LAMBDA(t,
SUM(TAKE(FILTER(score,team=t),@number))
)
)The approach is fairly 'heavy' in terms of its programming content and many traditional spreadsheet users may choose to stick with what they know.