Rank Formula

[sassriverrat]

Good Morning-

Quick question for someone-

I've got
numbers A1:A10
a "Y" or "N" in B1:B10
a "Y" or "N" in C1:C10

I'm trying to put a rank formula together that
IF B cell is a "Y", it goes to the top of the rank, irregardless of the number in A cell. Then if C cell is a "Y", it goes to the top of the rank AFTER the B cell "Y"- and finally, if both B and C cells are "N", then the final numbers are ranked by the numbers in "A"

Ranks are put in column D.

Purpose- the ranking is by the number in A (Level 1) but if B is a "Y", then it automatically goes to the top of the list (Level 2) and if C is a "Y", it automatically goes to the top of the list (Level 3).

 

[sassriverrat]

Rank	Name	Days	Is 2/M	Has Been 2/M
2	John	5	Y	Y
8	Kim	18	N	N
3	Austin	2	Y	Y
4	Jimmy	64	N	Y
6	Julia	8	N	Y
9	Eric	14	N	N
10	Morgan	1	N	N
5	Dan	25	N	Y
7	Mimi	32	N	N
1	Ashley	21	Y	Y

<tbody>
</tbody>

 

[Eric W]

Try this:

Excel 2012

	D	E	F	G	H	I
3	Rank		Name	Days	Is 2/M	Has Been 2/M
4	2		John	5	Y	Y
5	8		Kim	18	N	N
6	3		Austin	2	Y	Y
7	4		Jimmy	64	N	Y
8	6		Julia	8	N	Y
9	9		Eric	14	N	N
10	10		Morgan	1	N	N
11	5		Dan	25	N	Y
12	7		Mimi	32	N	N
13	1		Ashley	21	Y	Y

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D4 =IF(H4="y",COUNTIFS($H$4:$H$60,"Y",$G$4:$G$60,">"&G4)+1,IF(I4="y",COUNTIF($H$4:$H$60,"Y")+COUNTIFS($H$4:$H$60,"N",$I$4:$I$60,"Y",$G$4:$G$60,">"&G4)+1,COUNTIFS(H$4:$H$60,"N",$I$4:$I$60,"N",$G$4:$G$60,">"&G4)+SUMPRODUCT(--(($H$4:$H$60="Y")+($I$4:$I$60="Y")>0))+1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[DanteAmor]

Try:

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:82px;" /><col style="width:82px;" /><col style="width:82px;" /><col style="width:82px;" /><col style="width:82px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">RANK</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">NAME</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DAYS</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">IS 2/M</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">HAS BENN</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">2</td><td >JO</td><td style="text-align:right; ">5</td><td style="background-color:#ffff00; ">Y</td><td >Y</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">8</td><td >KI</td><td style="text-align:right; ">18</td><td >N</td><td >N</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">3</td><td >AU</td><td style="text-align:right; ">2</td><td style="background-color:#ffff00; ">Y</td><td >Y</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">4</td><td >JI</td><td style="text-align:right; ">64</td><td >N</td><td style="background-color:#b2a1c7; ">Y</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">6</td><td >JU</td><td style="text-align:right; ">8</td><td >N</td><td style="background-color:#b2a1c7; ">Y</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">9</td><td >ER</td><td style="text-align:right; ">14</td><td >N</td><td >N</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">10</td><td >MO</td><td style="text-align:right; ">1</td><td >N</td><td >N</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">5</td><td >DA</td><td style="text-align:right; ">25</td><td >N</td><td style="background-color:#b2a1c7; ">Y</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">7</td><td >MI</td><td style="text-align:right; ">32</td><td >N</td><td >N</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">7</td><td >AS</td><td style="text-align:right; ">21</td><td style="background-color:#ffff00; ">Y</td><td >Y</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E4</td><td >=IF(H4="Y",COUNTIFS($H$4:$H$60,H4,$G$4:$G$60,">"&G4)+1,IF(I4="Y",COUNTIFS($I$4:$I$60,I4,$H$4:$H$60,H4,$G$4:$G$60,">"&G4)+1+COUNTIF($H$4:$H$60,"Y"),COUNTIFS($H$4:$H$60,H4,$I$4:$I$60,I4,$G$4:$G$60,">"&G4)+1+SUMPRODUCT((($H$4:$H$60="Y")+($I$4:$I$60="Y")>0)+0)))</td></tr></table></td></tr></table> <br /><br />

 

[Marcelo Branco]

Another possible solution

	D​	E​	F​	G​	H​	I​
1​
2​
3​	Rank​		Name​	Days​	Is 2/M​	Has Been 2/M​
4​	2​		John​	5​	Y​	Y​
5​	8​		Kim​	18​	N​	N​
6​	3​		Austin​	2​	Y​	Y​
7​	4​		Jimmy​	64​	N​	Y​
8​	6​		Julia​	8​	N​	Y​
9​	9​		Eric​	14​	N​	N​
10​	10​		Morgan​	1​	N​	N​
11​	5​		Dan​	25​	N​	Y​
12​	7​		Mimi​	32​	N​	N​
13​	1​		Ashley​	21​	Y​	Y​
14​

Formula in D4 copied down
=SUMPRODUCT(--((H$4:H$13="Y")*10+(I$4:I$13="Y")+G$4:G$13/10^6>(H4="Y")*10+(I4="Y")+G4/10^6))+1

M.

 

[sassriverrat]

@Eric W

Worked like a champ! Thank you all for the help!

I tested the others which also worked- just so you guys know!

 

[Eric W]

Glad we could help!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.