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