Formula to Count number of wins between two players

[sankaman]

I have a table in which I want to count the number of matches a certain player has won and loss. This is what I have:

What I want is a formula that returns the number of times Alex has won and lost against all other players and so on with every player.

I was thinking of COUNTIF but I can seem to get it to work.

 

[Cubist]

For Alex, wouldn't it just be the count of column B? But then you have 9 values for all players. Not clear what the W/L in your data indicates.

 

[Michael M]

Maybe this way

=SUMIF(B2:U2, "W", B3:U3)

 

[sankaman]

Maybe this way

=SUMIF(B2:U2, "W", B3:U3)

Thanks, I've tried that and the result is the sum of points scored, not the matches won. Each pl;ayer plays agains all the other players and the poins are counted, I have that on another sheet. In this instance, the number of matches won by Alex in this example is 6 and the number of matches lost is 3 for a total of 9 played matches. The difference in points (column Z is -2. (total won points vs loss points).

 

[Cubist]

How can one tell wether Alex won or lost against GodFried when you have 2 points under W and 3 under L? Does this mean Alex won by 2 points?

 

[kevin9999]

Could you provide a copy of your sheet using the XL2BB - Excel Range to BBCode, or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform?

 

[RobOrBob]

I'm assuming W and L refer to points won or lost NOT the match?

So when Alex played against Godfried Alex won 3 points and Godfried won 2 with the result that Alex won the match (by 1 point).

So Alex has won 5 matches, drawn 1 and lost 3? Not 'In this instance, the number of matches won by Alex in this example is 6 and the number of matches lost is 3 for a total of 9 played matches.' you mentioned?

 

[RobOrBob]

If my earlier assumptions are correct, I would create a set of helpers (10 columns x 10 rows) to convert the points to whether the match was won/drawn/lost.

For example
=if(and(isnumber(b3),isnumber(c3)),sign(b3-c3),"")

so sign will return 1 for a Win, 0 for a Draw and -1 for a Loss.

You can then add up using for example COUNTIF(range,xn=1) to get the number of wins.

Again I'm making assumptions that the players are a fixed number.

 

[sankaman]

If my earlier assumptions are correct, I would create a set of helpers (10 columns x 10 rows) to convert the points to whether the match was won/drawn/lost.

For example
=if(and(isnumber(b3),isnumber(c3)),sign(b3-c3),"")

so sign will return 1 for a Win, 0 for a Draw and -1 for a Loss.

You can then add up using for example COUNTIF(range,xn=1) to get the number of wins.

Again I'm making assumptions that the players are a fixed number.

Thanks for your help.

The W or L refer to who won the match, there can't be a tie, I made a mistake there, it should be either a 3-2 in favor of Alex or 3-2 in favor of Jonathan. So each match has a winner and a loser and the sum of all the Won point are added and the total points Lost subtract to get the Point Difference. In this example, If Alex won a match against Godfried he wins one match point, and Godfried looses one match point.

 

[Peter_SSs]

The W or L refer to who won the match, there can't be a tie, I made a mistake there, it should be either a 3-2 in favor of Alex or 3-2 in favor of Jonathan. So each match has a winner and a loser and the sum of all the Won point are added and the total points Lost subtract to get the Point Difference.

What about this then?

24 04 06.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2		W	L	W	L	W	L	W	L	W	L	W	L	W	L	W	L	W	L	W	L			W	L	Pts
3	Alex			3	2	3	2	0	4	4	2	3	1	2	1	4	3	1	3	0	4			6	3	-2
WL

Cell Formulas
Range	Formula
X3	X3	=SUMPRODUCT((B3:T3>C3:U3)*(B$2:T$2="W"))
Y3	Y3	=COUNT(B3:U3)/2-X3
Z3	Z3	=SUMIF(B$2:U$2,"W",B3:U3)-SUMIF(B$2:U$2,"L",B3:U3)