Formula worked in google sheets but now doesnt work in Microsoft 365

[Morro91]

The formula is =ArrayFormula(SUM(IF(S11=B2:B23,H2:H23),{1,2,3}))

It was working in Google sheets, but now doesnt work in Microsoft 365 since i have transitioned.

What i am trying to do is find the last x couple of criteria (S11) in a column (B2:B23), and look for the numeric value that it corresponds to in another column (H2:H23).

Is this the right formula to even do it? specifically its for sports. So out of 1000 games. I would like to look in the full column, but to only find the last 5 games to find the average they are performing at. 

Thankyou

[#2]

@Morro91 

The formula in Excel should be like that.
=SUM(IF(S11=B2:B23,H2:H23,0)
For your second query, the formulas below will work whether there is any blank or not.

[#3]

@Starrysky1988 

Sorry if i wasnt clear, but attached is pretty much what i need. For the formula to have a criteria (team), and then look through the designated list, and find the last 3 scores (or how many i specify) in the corresponding scores column, then add them up, ignoring 0's or blanks.

apologies for not being more specific before

[#4 · Accepted solution]

@Morro91 

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")

[#5]

@Starrysky1988 

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

[#6]

COLUMN C is the third column in Excel and COLUMN(C$1) represents 3. You may change it accordingly.

[#7]

Legend thankyou so much mate!

[#8]

@Morro91 

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.

[#9]

@Morro91 

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)))))))