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")
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")
sorry but which part of the formula can i edit to specify how many games back i want to include? So if im looking for the last 3 games, how do i specify 3 only. If im looking for the last 4 games how do i specify that? i have a large list of fixtures, so have plenty of data. thankyou
- COLUMN C is the third column in Excel and COLUMN(C$1) represents 3. You may change it accordingly.
- Legend thankyou so much mate!
If you are office365 subscriber, you may write the single array formula as below.
=LET(T,A3:A18,S,B3:B18,G,3,U,UNIQUE(T),CHOOSE(SEQUENCE(1,2),U,MAP(U,LAMBDA(A,SUM(INDEX(FILTER(S,T=A),SEQUENCE(G)))))))