Rank w/ Multiple Tie Breakers

[rsieger]

Hi, I saw similar threads relating to Ranking w/ a single tie-breaker, but I'm getting tripped up on a formula that requires multiple tie-breakers.

<TABLE style="WIDTH: 178pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=236 border=0><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 1592" width=56><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 1649" width=58><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 1991" width=70><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1479" width=52><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl83 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 86pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=114 colSpan=2 height=17>A_______B_______ </TD><TD class=xl78 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=70>C_________ </TD><TD class=xl78 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=52>D______</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Driver</TD><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">BI</TD><TD class=xl80 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">COM</TD><TD class=xl80 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">COL</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>John</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1 </TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Paul</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>George</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Ringo</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD></TR></TBODY></TABLE>​

The Ranking criteria are as follows:
Highest # in column "B", if a tie, highest # in column "C", if tie, highest # in column "D". If there is still a tie, the Rank would be the order that the data appears on the list.

The Result should be as follows:
Rank Driver
1 Paul
2 George
3 Ringo
4 John

Can someone please show me a formula that produces the above result? Thanks in advance for your help!

 

[jbeaucaire]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Driver	BI	COM	COL	Rank	TieBrk			Standings	Driver
2	John	1	2	3	4	4			1	Paul
3	Paul	3	1	4	1	1			2	George
4	George	2	2	2	2	2			3	Ringo
5	Ringo	2	2	2	2	3			4	John
Sheet1

 

[jbeaucaire]

This formula may be slightly less calc-intense, it only triggers the tie-break calcs when there is a tie:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Driver	BI	COM	COL	Rank	TieBrk			Standings	Driver
2	John	1	2	3	4	4			1	Paul
3	Paul	3	1	4	1	1			2	George
4	George	2	2	2	2	2			3	Ringo
5	Ringo	2	2	2	2	3			4	John
6
Sheet1

 

[pgc01]

Hi jbeaucaire

If I'm not mistaken you are missing a term in your 2nd Sumproduct(). If B and C are equal, let D decide.

SUMPRODUCT(($B$2:$B$15=$B2)*($C$2:$C$15=$C2)*($D$2:$D$15>$D2))

 

[jbeaucaire]

This formula may be slightly less calc-intense, it only triggers the tie-break calcs when there is a tie:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Driver	BI	COM	COL	Rank	TieBrk	*	*	Standings	Driver
2	John	1	2	3	4	4	*	*	1	Paul
3	Paul	3	1	4	1	1	*	*	2	George
4	George	2	2	2	2	2	*	*	3	Ringo
5	Ringo	2	2	2	2	3	*	*	4	John
6	*	*	*	*	*	*	*	*	*	*
Sheet1

 

[pgc01]

Hi

jbeaucaire, doesn't always work for me, for instance with this example.

Another option:

In F2:

=IF(COUNTIF(E:E,E2)=1, E2, RANK($B2,$B$2:$B$15) + SUMPRODUCT(($B$2:$B$15=B2)*($C$2:$C$15>$C2))+SUMPRODUCT(($B$2:$B$15=$B2)*($C$2:$C$15=$C2)*($D$2:$D$15>$D2)))+SUMPRODUCT(($B$1:$B1=$B2)*($C$1:$C1=$C2)*($D$1:$D1=$D2))

In J2:´

=INDEX($A$2:$A$5,MATCH(I2,$F$2:$F$5,0))

Copy down


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >F</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >G</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >H</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >I</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >J</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >K</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;background:#FFFF00;text-align:center;border-width: 1px;border-color:#888888; ">Driver</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;background:#FFFF00;text-align:center;border-width: 1px;border-color:#888888; ">BI</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;background:#FFFF00;text-align:center;border-width: 1px;border-color:#888888; ">COM</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;background:#FFFF00;text-align:center;border-width: 1px;border-color:#888888; ">COL</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;background:#FFFF00;text-align:center;border-width: 1px;border-color:#888888; ">Rank</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;background:#FFFF00;text-align:center;border-width: 1px;border-color:#888888; ">Standings</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;font-weight: bold;background:#FFFF00;text-align:center;border-width: 1px;border-color:#888888; ">Driver</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">John</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Paul</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Paul</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">George</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">George</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">John</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Ringo</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Ringo</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=12 style="background:#9CF; padding-left:1em" > [Book1.xlsb]Sheet6</td></tr></table>

 

[rsieger]

Thank you pgc01 & jbeaucaire!!!! Worked like a charm!

 

[toeknee75]

Hi

jbeaucaire, doesn't always work for me, for instance with this example.

Another option:

In F2:

=IF(COUNTIF(E:E,E2)=1, E2, RANK($B2,$B$2:$B$15) + SUMPRODUCT(($B$2:$B$15=B2)*($C$2:$C$15>$C2))+SUMPRODUCT(($B$2:$B$15=$B2)*($C$2:$C$15=$C2)*($D$2:$D$15>$D2)))+SUMPRODUCT(($B$1:$B1=$B2)*($C$1:$C1=$C2)*($D$1:$D1=$D2))

In J2:´

=INDEX($A$2:$A$5,MATCH(I2,$F$2:$F$5,0))

Copy down


<table style="border-color: rgb(204, 204, 204); border-width: 2px; background: rgb(255, 255, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border-collapse: collapse; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="1"><tbody><tr><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> </th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">A</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;" width="30">B</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;" width="30">C</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;" width="30">D</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;" width="30">E</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;" width="30">F</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;" width="30">G</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;" width="30">H</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">I</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">J</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;" width="30">K</th></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; font-weight: bold; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">Driver</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; font-weight: bold; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">BI</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; font-weight: bold; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">COM</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; font-weight: bold; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">COL</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; font-weight: bold; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">Rank</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; font-weight: bold; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">Standings</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(255, 255, 0) none repeat scroll 0% 0%; font-weight: bold; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">Driver</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">John</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">3</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">Paul</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">3</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">Paul</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">3</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">4</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">George</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">4</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">George</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">4</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">3</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">John</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">5</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">Ringo</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">4</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: center;">4</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">Ringo</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; text-align: center;">6</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td></tr><tr><td colspan="12" style="background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; padding-left: 1em;"> [Book1.xlsb]Sheet6</td></tr></tbody></table>

PGC01,

Thanks for posting this.
This formula works for my needs as well. But I have a question about it.
Within the formula, what does the "E:E,E2)=1, E2," represent?
Since there's no data in that range?

Thanks,
Just trying to wrap my brain around it.
-Tony

 

[toeknee75]

NEVERMIND! I just scrolled up and re-read everything again... There's jsut a column missing in the example that was posted... Thanks!

 

[toeknee75]

Actually, that wasn't right either.... I'm confused as to why Column E is blank and is used in the formula...

-Tony