Goal difference for last 3 matches wanted

[tom hatten]

Hello.
I'm trying to find the goal difference for each team over a period of 3 weeks. With the fixtures showing at the top, I want the goal difference for the home and away teams to show in columns C and D respectively. For example, over the past 3 games Cardiff have scored 5 goals and conceded 7, goal difference -2. Brighton have scored 3 and conceded 3, goal difference 0.

Thank you.

[Special-K]

Where do you want the output to go, its not clear?

[tom hatten]

Sorry. The results for the home team in cell C3 and the away team in cell D3.
Thank you.

[Special-K]

Change the values in columns C and D to numbers, they're currently text so SUMPRODUCT isnt working.
Once youve done that

in C3
=(SUMPRODUCT(--(A$15:A$46=A3)*(C$15:C$46))+SUMPRODUCT(--(B$15:B$46=A3)*(D$15:D$46)))-(SUMPRODUCT(--(A$15:A$46=A3)*(D$15:D$46))+SUMPRODUCT(--(B$15:B$46=A3)*(C$15:C$46)))

There may be a shorter way

[tom hatten]

I've changed the values in columns C and D to numbers but I'm getting a #VALUE error in C3.

[Special-K]

Reenter the values again, and the formula works.
Dont just change the format, reenter the values.

[davsth]

i suggest posting the workbook that is not working or typing a 1 in a cell copying it and paste special multipying the values in columns c and d

[tom hatten]

I've typed 1 in a cell, copied it and used paste special with multiplying into cell C3 then pasted the formula into C3. It's still producing a #Value error.
Thank you.

[Special-K]

So use the other suggestion of renetering the values again

[Special-K]

File attached

[tom hatten]

It's kind of working now but some of the results are incorrect. Looking down the results, Tottenham have scored 4 and conceded 3 (goal difference 1) but it shows '4'.
Thank you.

[tom hatten]

Am I correct in thinking that I can copy the formula across into Cell D3?

[tom hatten]

The results in C3:C12 are correct, but the results in D3:D12 are wrong.
Thank you.

[tom hatten]

I've changed the formula in D3 from =(SUMPRODUCT(--(B$20:B$51=B8)*(D$20:D$51))+SUMPRODUCT(--(C$20:C$51=B8)*(E$20:E$51)))-(SUMPRODUCT(--(B$20:B$51=B8)*(E$20:E$51))+SUMPRODUCT(--(C$20:C$51=B8)*(D$20:D$51)))
to
=(SUMPRODUCT(--(B$20:B$51=B8)*(D$20:D$51))+SUMPRODUCT(--(A$20:A$51=B8)*(C$20:C$51)))-(SUMPRODUCT(--(B$20:B$51=B8)*(C$20:C$51))+SUMPRODUCT(--(A$20:A$51=B8)*(D$20:D$51)))
and it works fine now.
Thank you.