Re: Last 6 matches home and away soccer form table

Here is another option where you can use whole column references like D:D so you don't have to limit the ranges (in the case that they move around).

If you put the country in column A just before their series to identify the series, e.g. type ** Italy **(or Italia) in A1,

**(or Inghilterra) in A2309 (just before the England games)**

*England*Then you can use this formula in Z3:

=CONTA.PIÙ.SE(B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;D:D;Y3;K:K;">0")

This formula looks for "Italy" in column A, then takes the number in column B just after the work (it knows now this is the last round of the series) and subtracts 5 from that, so it knows the last 6 rounds... and it calculates from there. Now you don't have to adjust ranges, just change the text "Italy" when you apply formula somewhere else.

Hopefully that might help.