Forum Discussion
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
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")
8 Replies
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.
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.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
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")
