[SOLVED] Counting points before a date while ignoring blanks

Counting points before a date while ignoring blanks

I am trying to count totals from a table that contains points for football clubs in a league.

Row	Column A	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
...

sample spreadsheet

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.

Solution

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