Sum non adjacent cells with criteria

S

sooshil

Board Regular
Joined
Feb 21, 2013
Messages
104
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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]
 
Sort by date Sort by votes
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?
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
it is possible that under regina you have blank spaces
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Sports competition top scorer list (round by round)
2
Replies
17
Views
270
Kaper
Kaper
W
  • Question Question
Personnel Roster using Macro's
Replies
0
Views
332
womeclr
W
M
  • Question Question
Fantasy Premier League (FPL) - Swapping players for subs depending on minutes played
Replies
2
Views
963
MaccyW
M
F
  • Question Question
Scoresheet creation for a trivia game
Replies
1
Views
346
Rijnsent
Rijnsent
C
  • Solved
Need to Return Unique Players for Same Multiple Number Result (XLOOKUP?)
2
Replies
14
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,224,829
Messages
6,181,222
Members
453,024
Latest member
Wingit77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top