calculate how many wins

[Gazlar]

Hi Guys,
My question is I need to calculate how many wins, draws and loses a team has by just putting the score in for Example:
A B C D E
1 Kelmscott 2 v 1 Canning Vale
2 Gosnells utd 3 v 2 Armadale
3 Joondalup 0 v 2 Wanneroo
4 Armadale 0 v 1 Kelmscott
5 Canning Vale 2 v 0 Wanneroo
6 Joondalup 0 v 0 Gosnells

So in this example I would like to a formula to calculate:

1 G H I J
2 Team win Draw Lose
3 Kelmscott 2 0 0
4 Canning Vale 1 0 1
5 Joondalup 0 1 1
6 Gosnells 1 1 0


I can then calculate the points earned by each team i K 1,2,3,4,5 and 6.

Really its similar sort of thing as is used in league tobles.

Your help is much appreciated.

Thank You

Gazlar

 

[XOR LX]

Hi,

I can't tell from your table, but are the entries for e.g.

Kelmscott 2 v 1 Canning Vale

in separate columns or a single cell?

I mean, is "Kelmscott" in one column, followed by "2", "v", "1" and "Canning Vale", all in separate columns?

Regards

 

[Gazlar]

Yeah they are in separate columns.

 

[Matty]

Try these...

H2:

=SUM(IF(A$1:A$6=G2,IF(B$1:B$6 > D$1:D$6,1)))+SUM(IF(E$1:E$6=G2,IF(D$1:D$6 > B$1:B$6,1)))

I2:

=SUM(IF(A$1:A$6=G2,IF(B$1:B$6=D$1:D$6,1)))+SUM(IF(E$1:E$6=G2,IF(D$1:D$6=B$1:B$6,1)))

J2:

<d$1:d$6,1)))+sum(if(e$1:e$6=g2,if(d$1:d$6<b$1:b$6,1)))[ code]
<d$1:d$6,1)))+sum(if(e$1:e$6=g2,if(d$1:d$6<b$1:b$6,1)))
<d$1:d$6,1)))+sum(if(e$1:e$6=g2,if(d$1:d$6<b$1:b$6,1)))
=SUM(IF(A$1:A$6=G2,IF(B$1:B$6 < D$1:D$6,1)))+SUM(IF(E$1:E$6=G2,IF(D$1:D$6 < B$1:B$6,1)))

All of which require committing with CTRL+SHIFT+ENTER and can then be copied down.

Matty</d$1:d$6,1)))+sum(if(e$1:e$6=g2,if(d$1:d$6<b$1:b$6,1)))
</d$1:d$6,1)))+sum(if(e$1:e$6=g2,if(d$1:d$6<b$1:b$6,1)))
</d$1:d$6,1)))+sum(if(e$1:e$6=g2,if(d$1:d$6<b$1:b$6,1)))[>

 

[XOR LX]

Or, non-array alternatives:

For Win:

=SUMPRODUCT(($A$1:$A$6=$G2)*($B$1:$B$6>$D$1:$D$6))+SUMPRODUCT(($E$1:$E$6=$G2)*($B$1:$B$6<$D$1:$D$6))

For Draw:

=SUMPRODUCT(($A$1:$A$6=$G2)*($B$1:$B$6=$D$1:$D$6))+SUMPRODUCT(($E$1:$E$6=$G2)*($B$1:$B$6=$D$1:$D$6))

For Loss:

=SUMPRODUCT(($A$1:$A$6=$G2)*($B$1:$B$6<$D$1:$D$6))+SUMPRODUCT(($E$1:$E$6=$G2)*($B$1:$B$6>$D$1:$D$6))

Regards

 

[Gazlar]

The win and lose count work perfect with the Array formula, but the draw count counts draws for games that have no figures in them recognizing nothing in the cells for games that haven't played yet as draws.

the actual formula I'm using is as follows:
Draw
=SUM(IF($B$1:$B$100=$J2,IF($C$1:$C$100=$F$1:$F$100,1)))+SUM(IF($G$1:$G$100=$J2,IF($F$1:$F$100=$C$1:$C$100,1)))

 

[Gazlar]

I have tried the non-array formula but it didn't work, I've tried to put an extra if function in but it didn't work either.
it calculates the 2 blank cells as a draw.

I need it to not calculate unless there is an amount in the cell whether it be a zero or any other number.

Your help is much appreciated.

 

[XOR LX]

To be fair, your table did not include any examples of blank entries in the Score field.

Change your Draw formula to:

=SUMPRODUCT(($A$1:$A$6=$G2)*($B$1:$B$6=$D$1:$D$6)*($B$1:$B$6<>""))+SUMPRODUCT(($E$1:$E$6=$G2)*($B$1:$B$6=$D$1:$D$6)*($B$1:$B$6<>""))

Regards

 

[Gazlar]

Works an absolute treat. Thank you

 

[XOR LX]

You're welcome.