unique ranking

[Lister]

A B C D E F G H I
1 Players 1 2 3 4 Total pts Sets Won Rank
2 1 john X 2 1 2 5 9
3 2 james 1 X 1 2 4 5
4 3 mathew 2 2 X 1 5 5
5 4 ace 1 1 2 X 4 5


Hi I am sorry I don't know how to post to look like it's on excel. I want a unique ranking first base on total points, then ties will base on sets won and if there are still ties I will base them on win over the other. So in this case base on total points number 1 is john and Mathew then number 2 is james and ace. Now checking the sets won(on different data) number 1 is john and number 2 is Mathew then number 3 are james and ace. then lastly checking on the games James (2) Vs Ace (4) winner is james scoring 2 points against ace only 1 point. there number 3 is james and 4 is ace. I need a formula for this. Thanks a lot for helping.

 

[Lister]

	a	b	c	d	e	f	g	h	i
1	Bracket	A	1	2	3	4	total points	sets won	rank
2	1	john	x	2	1	2	5	9
3	2	james	1	x	1	2	4	5
4	3	mathew	2	2	x	1	5	5
5	4	ace	1	1	2	x	4	5

I hope this table lets you imagine what I want..thanks.

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Bracket​	Players​	1​	2​	3​	4​	Total pts​	Sets Won​	Rank​	TieBreaker​	Final Rank​
2​	1​	john​	X​	2​	1​	2​	5​	9​	1​	0​	1​
3​	2​	james​	1​	X​	1​	2​	4​	5​	3​	0​	3​
4​	3​	mathew​	2​	2​	X​	1​	5​	5​	2​	0​	2​
5​	4​	ace​	1​	1​	2​	X​	4​	5​	3​	1​	4​

Formula in I2 copied down
=COUNTIF($G$2:$G$5,">"&G2)+COUNTIFS(G$2:G$5,G2,H$2:H$5,">"&H2)+1

Array formula in J2 copied down
=IF(COUNTIF(I$2:I$5,I2)=2,IF(INDEX($C$2:$F$5,SMALL(IF(ROW(I$2:I$5)<>ROW(I2),IF(I$2:I$5=I2,A$2:A$5)),1),A2)>INDEX($C$2:$F$5,A2,SMALL(IF(ROW(I$2:I$5)<>ROW(I2),IF(I$2:I$5=I2,A$2:A$5)),1)),1,0),0)
Ctrl+Shift+Enter

Formula in K2 copied down
=I2+J2

Remark
It works with a tie between 2 players. If a tie occurs between 3 players, an unresolved case can be reached. for example:
Player 1 beats Player 2; Player 2 beats player 3; Player 3 beats Player 1.
Flip a coin?

Hope this helps

M.