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]

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

I
t's easier to do two columns than one. From your sample results, team 1 should return 35

 

[scottwisler]

same thing. Team 8 shows 35 W's, but should be 0. =SUMPRODUCT(--(LEFT(A12:A45,1)="8")+(RIGHT(A12:A45,1)="8"),--(D12:D45>E12:E45)+(F12:F45>G12:G45)+(H12:H45>I12:I45)+(J12:J45>K12:K45)+(L12:L45>M12:M45))

 

[scottwisler]

I
t's easier to do two columns than one. From your sample results, team 1 should return 35

Exactly, and team 8 should be 0, but it calcs 35 for team 8 as well

 

[scottwisler]

OK, so how would I correctly syntax the addition of the 2 of these: =SUMPRODUCT(--(B12:B45="8 ")+(C12:C45="8 "),--(D12:D45>E12:E45)+(F12:F45>G12:G45)+(H12:H45>I12:I45)+(J12:J45>K12:K45)+(L12:L45>M12:M45)) and this: =SUMPRODUCT(--(B12:B45="8 ")+(C12:C45="8 "),--(D12:D45<E12:E45)+(F12:F45<G12:G45)+(H12:H45<I12:I45)+(J12:J45<K12:K45)+(L12:L45<M12:M45)). that gives me the correct calc, b/c if the 2nd team wins, the <> calc has to be reversed.

 

[scottwisler]

OK I think I may have it: =(SUMPRODUCT(--(B12:B45="8 "),--(D12:D45>E12:E45)+(F12:F45>G12:G45)+(H12:H45>I12:I45)+(J12:J45>K12:K45)+(L12:L45>M12:M45)))+(SUMPRODUCT(--(C12:C45="8 "),--(D12:D45<E12:E45)+(F12:F45<G12:G45)+(H12:H45<I12:I45)+(J12:J45<K12:K45)+(L12:L45<M12:M45)))<e12:e45)+(f12:f45<g12:g45)+(h12:h45<i12:i45)+(j12:j45<k12:k45)+(l12:l45<m12:m45)))< html=""></e12:e45)+(f12:f45<g12:g45)+(h12:h45<i12:i45)+(j12:j45<k12:k45)+(l12:l45<m12:m45)))<>

 

[Brian from Maui]

Copy and Paste this in the summary cell for team 8. This "assumes" team numbers in B & C. Make sure B & C are numeric, not text. What is your result?

=SUMPRODUCT(--(B2:B45=1)+(C2:C45=1),--(D2:D45>E2:E45)+(F2:F45>G2:G45)+(H2:H45>I2:I45)+(J2:J45>K2:K45)+(L2:L45>M2:M45))

 

[scottwisler]

my post above actually worked...thank you SOOOOOOO..... much for your assistance Brian!

 

[Brian from Maui]

Thanks but you figured it out on your own. Please be careful with text and numeric when using as a criteria and expra spaces. It may return skewed results.

For my sanity, please try the solution I gave. Thanks!