How to get rolling sum for last 3 results where key value is either in one or another column in google spreadsheets

user_8103292 0
Viewed 89

my table looks like this:

Home team Away team Home index Away index home rolling score away rolling score
Philadelphia Atlanta 1 0 0 0
New York Houston 0 1 0 0
Denver Orlando 1 0 0 0
Philadelphia Atlanta 1 0 1 0
New York Houston 0 1 0 1 1
Los Angeles Atlanta 1 0 0 0
Philadelphia Atlanta 1 0 2 0
New York Philadelphia 0 1 0 3
San Antonio Dallas 1 0 1 0
Philadelphia Atlanta 1 0 3 0
New York Philadelphia 0 1 0 3
Miami Boston 1 0 1 0
Philadelphia Atlanta 1 0 1 0
New York Houston 0 1 0 1

I would like to get a rolling sum for last 3 results for Philadelphia for example for all of their matches, so if they played at home then the home index number should count, if they played away, away index should count.

The result should come in another column where the home team has calculated their rolling sum and also do the same for away team until that row (not including that row). This means in every row the query will look for a home team and calculate the rolling sum until that row. So as in the example you can see home rolling score and away rolling score. If you check the Philadelphia score, it's 0 in the first row because there are no records before then in the next game of Philadelphia it's 1 because that's the sum of their previous games and in row 10 where there is game Philadelphia vs Atlanta, the rolling sum for home is 3 which is the sum of last 3 rows for Philadelphia without counting that row.

Any help how can I achieve this is much appreciated.

google-sheets
2 Answers

overal:

=QUERY(FILTER({FLATTEN(A2:B20), FLATTEN(C2:D20)}, 
 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 label sum(Col2)''", )

enter image description here

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

enter image description here

user_56326290

I believe this is what you are looking for:

ex

=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.

user_118612180
Related