Football form

Hi all

I would like to create a formula that counts in a way the form of the teams. For example, in the attachment, on the second row we see the Liverpool-Norwich match that ended 4-1. Each column H:AA, refers to one of the teams and at each row the goal difference for each of their match is calculated, e.g. in row 2, Liverpool's column has 3 and Norwich's column has -3.After 6 matchdays, we go to row 62 and the match Sheffield United-Liverpool. Sheffield's goal difference -as seen on Sheffield's column- is 0+1-1+0-1+2 =1 and Liverpool's is 3+1+2+3+2+1=12. The formula I am asking for, starting from cell G62, will be calculating the goal difference of the home team's last 6 matches minus the goal difference of the away team's last 6 matches. For the "Sheffield United-Liverpool" example, the sum would be -11.

Columns H:AA were my idea, thought it might be easier, if anyone can come to a solution from a different way, they are welcome.

Kind regards,

So, don't laugh but for row 62 i got what I wanted with:

=SUMPRODUCT(HLOOKUP($B62;$H$1:$AA61;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61}))-SUMPRODUCT(HLOOKUP($C62;$H$1:$AA61;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61}))

There MUST be an easier and less time-consuming way!!

It's OK, I solved it.

Thanks anyway.

Quote from royUK

It's usuall to post the solution even if you solved it yourself It may help someone else

Good point, would do it if someone was interested. Solution is on G:G. It uses data from I:AB, which is an array formula that goes

=SUM(INDEX(AC$3:AC52;LARGE(IF(AC$3:AC52<>"";ROW(AC$3:AC52)-ROW(AC$3)+1);5)):AC52)

If anyone can shorten that even more, feel free to do so

Thanks