Sum non adjacent cells with criteria

[sooshil]

Hello

I have data to analyse game result as follows.
Every team has to play twice with the same opponent yielding Score1 and Score2.
I have to add total score of each player on a table 2 shown below.
The main problem is the number of team will be changing. If a team is added then every team will also play with the new team.
So, I want to dynamically add those team scores no matter how many times they played.
I have list of team in another sheet and this first data is dynamically generated based upon the teams listed on the other sheet.
Since the player can be added or removed, the number of scores we need to take will also be changed. That means the position(cell address) of a player in the sheet changes as a player is added or removed.
May be I am explaining more than required. If required more information, I am ready here.


[TABLE="class: grid, width: 1260"]
<colgroup><col span="2"><col><col><col span="2"><col span="7"><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="colspan: 2"]John Vs Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]John Vs Joseph[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]John Vs Regina[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Teams[/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[TD]Tie?[/TD]
[TD]N/R?[/TD]
[TD][/TD]
[TD]Teams[/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[TD]Tie?[/TD]
[TD]N/R?[/TD]
[TD][/TD]
[TD]Teams[/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[TD]Tie?[/TD]
[TD]N/R?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John[/TD]
[TD]25[/TD]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]35[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]38[/TD]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jane[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Joseph[/TD]
[TD]46[/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Regina[/TD]
[TD]42[/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="colspan: 2"]Jane Vs Joseph[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Jane Vs Regina[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Joseph Vs Regina[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Teams[/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[TD]Tie?[/TD]
[TD]N/R?[/TD]
[TD][/TD]
[TD]Teams[/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[TD]Tie?[/TD]
[TD]N/R?[/TD]
[TD][/TD]
[TD]Teams[/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[TD]Tie?[/TD]
[TD]N/R?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Jane[/TD]
[TD]25[/TD]
[TD]52[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD]40[/TD]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Joseph[/TD]
[TD]42[/TD]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Joseph[/TD]
[TD]36[/TD]
[TD]41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Regina[/TD]
[TD]32[/TD]
[TD]51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Regina[/TD]
[TD]44[/TD]
[TD]41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joseph[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Regina[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sooshil]

I am getting Run-time Error 1004
Application defined or object defined error

When I click Debug, it yellow highlights line
c.Offset(, 2).Value = c.Offset(, 2).Value + f.Offset(n, 1).Value

Even if there is error, it sums the values. But, it adds 0 to the next empty cells of For columns of both Score1 and Score2 as shown in the table below.
[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Score1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Score2[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]For[/TD]
[TD]Against[/TD]
[TD]For[/TD]
[TD]Against[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]15.6[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]17.5[/TD]
[TD="align: right"]12.9[/TD]
[/TR]
[TR]
[TD]Joseph[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]11.6[/TD]
[TD="align: right"]14.1[/TD]
[/TR]
[TR]
[TD]Regina[/TD]
[TD="align: right"]149[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]13.1[/TD]
[TD="align: right"]17.6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What did I do wrong?

 

[DanteAmor]

it is possible that under regina you have blank spaces