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

[scottwisler]

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>

 

[Brian from Maui]

It's doing bascially the same thing your original formula is. It checks for Team 1 in both columns, then counts how many "wins" in the columns housing the scores. You'll have to extend the last part of the formula to count the three remaining games.

It's OR'ing so you don't have to use SUMPRODUCT everytime you use the +

 

[scottwisler]

but will it catch it if the team is in the 2nd column? for instance, team 8 never appears in the 1st column, so the win calculation would be reversed if it is in the 2nd column

 

[Brian from Maui]

It should. Have your tried it? If it's not bringing back what you expect, post the results.

 

[scottwisler]

i'm getting #N/A: =SUMPRODUCT(--(B12:B45=C13)+(C1:C45=C13),--(D12:D45>E12:E45)+(F12:F45>G12:G45)+(H12:H45>I12:I45)+(J12:J45>K12:K45)+(L12:L45>M12:M45)) where B is the 1st team listed in the matchup's column, C is the 2nd team listed in the matchup...then the scores, for / against are in d/e, f/g, h/I, j/k, l/m respectively

 

[scottwisler]

Thanks again, btw...I am so frustrated with this at this point OH, and cell c13 is simply calculating wins for team 8, which in the sched above is listed in cell C13

 

[scottwisler]

OK I fixed the error (C1:C45 shb C12:C45...oops!) but it is not calculating correctly...I am now getting 35, which should be 0. it is only calculating correctly if the team being calculated is listed first...exactly the same way as my original.

 

[Brian from Maui]

i'm getting #N/A: =SUMPRODUCT(--(B12:B45=C13)+(C1:C45=C13),--(D12:D45>E12:E45)+(F12:F45>G12:G45)+(H12:H45>I12:I45)+(J12:J45>K12:K45)+(L12:L45>M12:M45)) where B is the 1st team listed in the matchup's column, C is the 2nd team listed in the matchup...then the scores, for / against are in d/e, f/g, h/I, j/k, l/m respectively

Change the range C1:C45 to C12:C45

 

[scottwisler]

I caught that already...see post above...thx!

 

[Brian from Maui]

i'm getting #N/A: =SUMPRODUCT(--(B12:B45=C13)+(C1:C45=C13),--(D12:D45>E12:E45)+(F12:F45>G12:G45)+(H12:H45>I12:I45)+(J12:J45>K12:K45)+(L12:L45>M12:M45)) where B is the 1st team listed in the matchup's column, C is the 2nd team listed in the matchup...then the scores, for / against are in d/e, f/g, h/I, j/k, l/m respectively

OK I see my error, you have 1 vs 2 in one cell. If that's the case, try

=SUMPRODUCT(--(LEFT(D1:D100,1)="1")+(RIGHT(D1:D100,1)="1"),--(G1:G100>H1:H100)+(I1:I100>J1:J100))

Adjust the ranges and criteria

The LEFT and RIGHT function returns a text value, so be careful when you do the summary sheet

 

[scottwisler]

I actually added 2 columns in which I just did "1 " and "2 " with spaces after to avoid cluttering the formulas with the left/right items, but let me give yours a shot...please hold