Please help
Col A Col B Col C Col D Col E Col F
Team A Team B 3 2 Team A 1
Team C Team D 2 1 Team B 1
Team A Team C 0 0 Team C 1
Team D Team B 1 2 Team D 1
Team D Team A 2 1
Team B Team C

The above is a fixture & result table of a €˜Round Robin soccer competition
where columns A&B are the fixtures & column C & D are the scores of the
respective games. Column E shows the teams in a league table format and
column F is the number of wins for each team. What I need is a formula to
calculate the number of wins for each team bearing in mind they maybe either
the home or away team (either column A or Column B)
Thanks in advance
Al

Hi,

To work out the wins use this where e1 is the team your lookinh for

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6$D$1:$D$6))+S UMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6$C$1:$C$6))

Drag down for other teams in Col E

To get draws you simply alter the formla to

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6=$D$1:$D$6))+S UMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6=$C$1:$C$6))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"gramps" wrote:

Please help
Col A Col B Col C Col D Col E Col F
Team A Team B 3 2 Team A 1
Team C Team D 2 1 Team B 1
Team A Team C 0 0 Team C 1
Team D Team B 1 2 Team D 1
Team D Team A 2 1
Team B Team C

The above is a fixture & result table of a €˜Round Robin soccer competition
where columns A&B are the fixtures & column C & D are the scores of the
respective games. Column E shows the teams in a league table format and
column F is the number of wins for each team. What I need is a formula to
calculate the number of wins for each team bearing in mind they maybe either
the home or away team (either column A or Column B)
Thanks in advance
Al

Thanks Mike thats exactly what I wanted.Much appreciated.
Al

"Mike H" wrote:

Hi,

To work out the wins use this where e1 is the team your lookinh for

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6$D$1:$D$6))+S UMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6$C$1:$C$6))

Drag down for other teams in Col E

To get draws you simply alter the formla to

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6=$D$1:$D$6))+S UMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6=$C$1:$C$6))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"gramps" wrote:

Please help
Col A Col B Col C Col D Col E Col F
Team A Team B 3 2 Team A 1
Team C Team D 2 1 Team B 1
Team A Team C 0 0 Team C 1
Team D Team B 1 2 Team D 1
Team D Team A 2 1
Team B Team C

The above is a fixture & result table of a €˜Round Robin soccer competition
where columns A&B are the fixtures & column C & D are the scores of the
respective games. Column E shows the teams in a league table format and
column F is the number of wins for each team. What I need is a formula to
calculate the number of wins for each team bearing in mind they maybe either
the home or away team (either column A or Column B)
Thanks in advance
Al

Sorry to be a pain but can you tell me how to adjust the formula as at the
moment unplayed games (no scores entered in Columns c & D) are counted as
draws.
Thanks.
Al

"Mike H" wrote:

Hi,

To work out the wins use this where e1 is the team your lookinh for

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6$D$1:$D$6))+S UMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6$C$1:$C$6))

Drag down for other teams in Col E

To get draws you simply alter the formla to

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6=$D$1:$D$6))+S UMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6=$C$1:$C$6))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"gramps" wrote:

Please help
Col A Col B Col C Col D Col E Col F
Team A Team B 3 2 Team A 1
Team C Team D 2 1 Team B 1
Team A Team C 0 0 Team C 1
Team D Team B 1 2 Team D 1
Team D Team A 2 1
Team B Team C

The above is a fixture & result table of a €˜Round Robin soccer competition
where columns A&B are the fixtures & column C & D are the scores of the
respective games. Column E shows the teams in a league table format and
column F is the number of wins for each team. What I need is a formula to
calculate the number of wins for each team bearing in mind they maybe either
the home or away team (either column A or Column B)
Thanks in advance
Al