Hi
Although this is posted as a new thread it is really a supplementary
question a reply fro0m Mike H.
I show again an extract from my worksheet and the formula to get the draws
from the fixtures.

=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))

My question is that this formula works fine for games that have already been
played & results entered in Columns C&D, however also assumes that unplayed
games where no results have been entered are also draws and so can you
suggest a simple amendment to the formula to disregard games where no result
has been entered.

Col A Col B Col C Col D Col E Col F
Team A Team B 3 2 Team A
Team B
Team C Team D 2 1 Team C
Team A Team C 0 0 Team D
Team D Team B 1 2
Team D Team A 2 1
Team B Team C
Many thanks for your help & appologies for not spotting this at the time.
Al

Hi,

I think we can assume that if there is no score in one of the columns then
the match hasn't been played so there will be no score in the second so in
reality can probably get away with just checking one column (C in this case)

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6<"")*($C$1:$C $6=$D$1:$D$6))+SUMPRODUCT(($B$1:$B$6=E1)*($C$1:$C$ 6<"")*($D$1:$D$6=$C$1:$C$6))

Blank cells will not now evaluate as a draw. The formula has line wrapped in
this post, it's all one line.
--
Mike

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


"gramps" wrote:

Hi
Although this is posted as a new thread it is really a supplementary
question a reply fro0m Mike H.
I show again an extract from my worksheet and the formula to get the draws
from the fixtures.

=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))

My question is that this formula works fine for games that have already been
played & results entered in Columns C&D, however also assumes that unplayed
games where no results have been entered are also draws and so can you
suggest a simple amendment to the formula to disregard games where no result
has been entered.

Col A Col B Col C Col D Col E Col F
Team A Team B 3 2 Team A
Team B
Team C Team D 2 1 Team C
Team A Team C 0 0 Team D
Team D Team B 1 2
Team D Team A 2 1
Team B Team C
Many thanks for your help & appologies for not spotting this at the time.
Al

Hi,
try

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6=$D$1:$D$6)*($ C$1:$C$6<"")*($D$1:$D$6<""))+SUMPRODUCT(($B$1:$B $6=E1)*($D$1:$D$6=$C$1:$C$6)*($C$1:$C$6<"")*($D$1 :$D$6<""))


"gramps" wrote:

Hi
Although this is posted as a new thread it is really a supplementary
question a reply fro0m Mike H.
I show again an extract from my worksheet and the formula to get the draws
from the fixtures.

=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))

My question is that this formula works fine for games that have already been
played & results entered in Columns C&D, however also assumes that unplayed
games where no results have been entered are also draws and so can you
suggest a simple amendment to the formula to disregard games where no result
has been entered.

Col A Col B Col C Col D Col E Col F
Team A Team B 3 2 Team A
Team B
Team C Team D 2 1 Team C
Team A Team C 0 0 Team D
Team D Team B 1 2
Team D Team A 2 1
Team B Team C
Many thanks for your help & appologies for not spotting this at the time.
Al

Thanx guys works a treat
Al

"gramps" wrote:

Hi
Although this is posted as a new thread it is really a supplementary
question a reply fro0m Mike H.
I show again an extract from my worksheet and the formula to get the draws
from the fixtures.

=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))

My question is that this formula works fine for games that have already been
played & results entered in Columns C&D, however also assumes that unplayed
games where no results have been entered are also draws and so can you
suggest a simple amendment to the formula to disregard games where no result
has been entered.

Col A Col B Col C Col D Col E Col F
Team A Team B 3 2 Team A
Team B
Team C Team D 2 1 Team C
Team A Team C 0 0 Team D
Team D Team B 1 2
Team D Team A 2 1
Team B Team C
Many thanks for your help & appologies for not spotting this at the time.
Al