How to get rolling sum for last 3 results where key value is either in one or another column in goog-CodePudding

The

pivoted:

=QUERY(FILTER({FLATTEN(A2:B20), FLATTEN(C2:D20), FLATTEN({"Home", "Away"}&IFERROR(ROW(A2:A20)/0))}, COUNTIFS(FLATTEN(A2:B20), FLATTEN(A2:B20), SEQUENCE(ROWS(A2:A20)*2), ">="&SEQUENCE(ROWS(A2:A20)*2))<4), "select Col1,sum(Col2) where Col2>0 group by Col1 pivot Col3", )

CodePudding user response：

I believe this is what you are looking for:

=sum(query(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("A"&row())),), "select * offset "&if(counta(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("A"&row())),))>3, counta(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("A"&row())),))-3, 0)))

=sum(query(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("B"&row())),), "select * offset "&if(counta(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("B"&row())),))>3, counta(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("B"&row())),))-3, 0)))

The formulas are placed in cells G2 and H2 respectively, and are copied down the column. I was unable to get these to work as an array formula for the entire columns, but it may be possible.

I separated them so they could be better understood, but you could also combine them like this

={sum(query(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("A"&row())),), "select * offset "&if(counta(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("A"&row())),))>3, counta(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("A"&row())),))-3, 0))),sum(query(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("B"&row())),), "select * offset "&if(counta(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("B"&row())),))>3, counta(ifna(filter(flatten(indirect("C1:D"&row()-1)), flatten(indirect("A1:B"&row()-1))=indirect("B"&row())),))-3, 0)))}

Placing this formula into cell G2 and copying it down the column (autofilling H).

This formula finds the rolling score for the last 3 games of a given team.