I am trying to count totals from a table that contains points for football clubs in a league.
Row | Column A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | ... |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Date: | 9/18 | 8/10 | 8/17 | 8/20 | 8/23 | 8/24 | 8/26 | 8/31 | 9/7 | 9/10 | 9/14 | 9/17 | 9/21 | 9/24 | 9/28 | ||
2 | AFC Fylde | 4 | 1NL | 3 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 3 | 1 | 0 | 0 | |||
3 | Aldershot Town | 8 | 1NL | 1 | 3 | 0 | 3 | 0 | 3 | 0 | 1 | 1 | 1 | 0 | 1 | |||
... | ||||||||||||||||||
27 | PL | W | D | L | Pts | |||||||||||||
28 | AFC Fylde | 6 | 1 | 1 | 4 | 4 | ||||||||||||
39 | Aldershot Town | 6 | 2 | 2 | 2 | 8 | ||||||||||||
... |
I would like it so whichever date was input to cell C1
, cells C2:C25
would add up the points for the six previous games before the date in C1
. Not every team plays on every date, so blanks should be ignored when collecting the six previous games.
Wins get 3, draws get 1 and losses get 0. The points should be collated into a table starting row 28.
In the table above, the points that should be included in the count are in bold.
To begin, I tried offset()
at the end of the season but cannot get it to miss out the blanks.
Use filter()
and choosecols()
, like this:
Points:
=sum(
choosecols(
hstack(
wraprows(,6,),
ifna(filter(E2:2, E$1:$1 <= C$1, len(E2:2)))
),
-1, -2, -3, -4, -5, -6
)
)
Plays:
=counta(
choosecols(
hstack(
wraprows(,6,),
ifna(filter(E2:2, E$1:$1 <= C$1, len(E2:2)))
),
-1, -2, -3, -4, -5, -6
)
)
Wins:
=countif(
choosecols(
hstack(
wraprows(,6,),
ifna(filter(E2:2, E$1:$1 <= C$1, len(E2:2)))
),
-1, -2, -3, -4, -5, -6
),
3
)
Draws:
=countif(
choosecols(
hstack(
wraprows(,6,),
ifna(filter(E2:2, E$1:$1 <= C$1, len(E2:2)))
),
-1, -2, -3, -4, -5, -6
),
1
)
Losses:
=countif(
choosecols(
hstack(
wraprows(,6,),
ifna(filter(E2:2, E$1:$1 <= C$1, len(E2:2)))
),
-1, -2, -3, -4, -5, -6
),
0
)
Points (the other way):
=3 * E28 + F28
See filter(), choosecols(), hstack(), wraprows() and ifna().