# new member..need help calculating # of Wins/Losses

#### scottwisler

##### New Member
Hello all! thanks in advance for any assistance! I am using Excel 2003 and trying to calculate the number of wins based on a set schedule of 5 game matches as follows: (MATCH - UPS is in A11, etc) I previously only had 3 games and nested if/than,s but since I am in 2003 and now have 2 more games in each matchup, the formula is too long.
 MATCH - UPS Game 1 Game 2 Game 3 Game 4 Game 5 1 vs 3 1 3 21 20 21 20 21 20 21 20 21 20 6 vs 8 6 8 21 20 21 20 21 20 21 20 21 20 5 vs 7 5 7 21 20 21 20 21 20 21 20 21 20 2 vs 4 2 4 21 20 21 20 21 20 21 20 21 20 6 vs 7 6 7 21 20 21 20 21 20 21 20 21 20 5 vs 8 5 8 21 20 21 20 21 20 21 20 21 20 2 vs 3 2 3 21 20 21 20 21 20 21 20 21 20 1 vs 4 1 4 21 20 21 20 21 20 21 20 21 20 1 vs 5 1 5 21 20 21 20 21 20 21 20 21 20 2 vs 6 2 6 21 20 21 20 21 20 21 20 21 20 4 vs 8 4 8 21 20 21 20 21 20 21 20 21 20 3 vs 7 3 7 21 20 21 20 21 20 21 20 21 20 2 vs 5 2 5 21 20 21 20 21 20 21 20 21 20 3 vs 8 3 8 21 20 21 20 21 20 21 20 21 20 1 vs 6 1 6 21 20 21 20 21 20 21 20 21 20 4 vs 7 4 7 21 20 21 20 21 20 21 20 21 20 4 vs 6 4 6 21 20 21 20 21 20 21 20 21 20 1 vs 7 1 7 21 20 21 20 21 20 21 20 21 20 2 vs 8 2 8 21 20 21 20 21 20 21 20 21 20 3 vs 5 3 5 21 20 21 20 21 20 21 20 21 20 2 vs 7 2 7 21 20 21 20 21 20 21 20 21 20 4 vs 5 4 5 21 20 21 20 21 20 21 20 21 20 1 vs 8 1 8 21 20 21 20 21 20 21 20 21 20 3 vs 6 3 6 21 20 21 20 21 20 21 20 21 20 3 vs 4 3 4 21 20 21 20 21 20 21 20 21 20 1 vs 2 1 2 21 20 21 20 21 20 21 20 21 20 5 vs 6 5 6 21 20 21 20 21 20 21 20 21 20 7 vs 8 7 8 21 20 21 20 21 20 21 20 21 20

<colgroup><col width="219" style="width: 164pt; mso-width-source: userset; mso-width-alt: 8009;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;" span="2"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;" span="5"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;" span="4"> <tbody>
</tbody>

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### scottwisler

##### New Member
here is where I need to calculate the # of wins:
 WINS LOSSES POINTS PLACE Team #1 35 0 35 1 Team #2 30 3 25 2 Team #3 25 6 15 3 Team #4 20 9 5 4 Team #5 15 12 -5 5 Team #6 10 15 -15 6 Team #7 5 18 -25 7 Team #8 0 21 -35 8

<colgroup><col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="64" style="width: 48pt;" span="2"> <col width="64" style="width: 48pt;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <tbody>
</tbody>

#### scottwisler

##### New Member
I have the following and it calc OK for the instances where the team in the matchup is always listed first (Team 1). =SUMPRODUCT(--('Scores-CT#1'!\$B\$12:\$B\$45=\$P14),--('Scores-CT#1'!\$D\$12:\$D\$45>'Scores-CT#1'!\$E\$12:\$E\$45))+SUMPRODUCT(--('Scores-CT#1'!\$B\$12:\$B\$45=\$P14),--('Scores-CT#1'!\$F\$12:\$F\$45>'Scores-CT#1'!\$G\$12:\$G\$45))+SUMPRODUCT(--('Scores-CT#1'!\$B\$12:\$B\$45=\$P14),--('Scores-CT#1'!\$H\$12:\$H\$45>'Scores-CT#1'!\$I\$12:\$I\$45))+SUMPRODUCT(--('Scores-CT#1'!\$B\$12:\$B\$45=\$P14),--('Scores-CT#1'!\$J\$12:\$J\$45>'Scores-CT#1'!\$K\$12:\$K\$45))+SUMPRODUCT(--('Scores-CT#1'!\$B\$12:\$B\$45=\$P14),--('Scores-CT#1'!\$L\$12:\$L\$45>'Scores-CT#1'!\$M\$12:\$M\$45))

#### scottwisler

##### New Member
I am sure there is a much more efficient way of calculating # of wins in this that I am overlooking?

#### Brian from Maui

##### MrExcel MVP

Maybe something like

=SUMPRODUCT(--(D1:D100="a")+(E1:E100="a"),--(G1:G100>H1:H100)+(I1:I100>J1:J100))

#### scottwisler

##### New Member
hmmm I am not sure if I follow you here. So the D1:D100="a" ..... I assume you mean the D column would be the first team listed in the matchup in the example? What is the "a" I apologize for the ignorance

#### scottwisler

##### New Member

any other contributions ?

#### Brian from Maui

##### MrExcel MVP
hmmm I am not sure if I follow you here. So the D1:D100="a" ..... I assume you mean the D column would be the first team listed in the matchup in the example? What is the "a" I apologize for the ignorance

My bad, Column D & E houses team names. A was just an example.

#### scottwisler

##### New Member
OK so you are just referring the point totals achieved in each game to one team or the other, but then what is the last part attempting to achieve? Sorry but I am a bit lost with this

#### scottwisler

##### New Member
So basically there are 8 teams. Each team plays round robin style, all other 7 teams based on the schedule below, but plays 5 games each week. the schedule is recorded as illustrated above with points for the first team listed, then points for the 2nd team listed...thx just wanted to clarify

Replies
1
Views
66
Replies
5
Views
75
Replies
8
Views
140
Replies
3
Views
50
Replies
3
Views
47

1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

### 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.

### Which adblocker are you using?

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

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