SOLVED

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

Copper Contributor

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

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

8 Replies

# Re: Formula worked in google sheets but now doesnt work in Microsoft 365

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.

# Re: Formula worked in google sheets but now doesnt work in Microsoft 365

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

best response confirmed by Morro91 (Copper Contributor)
Solution

# Re: 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")

# Re: Formula worked in google sheets but now doesnt work in Microsoft 365

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

# Re: Formula worked in google sheets but now doesnt work in Microsoft 365

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

# Re: Formula worked in google sheets but now doesnt work in Microsoft 365

Legend thankyou so much mate!

# Re: Formula worked in google sheets but now doesnt work in Microsoft 365

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.

# Re: Formula worked in google sheets but now doesnt work in Microsoft 365

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