Rank Formula

S

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
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).
 
Sort by date Sort by votes
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]Days[/TD]
[TD]Is 2/M[/TD]
[TD]Has Been 2/M[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]5[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Kim[/TD]
[TD]18[/TD]
[TD]N[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Austin[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]4 [/TD]
[TD]Jimmy[/TD]
[TD]64[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Julia[/TD]
[TD]8[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Eric[/TD]
[TD]14[/TD]
[TD]N[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Morgan[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Dan[/TD]
[TD]25[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]7 [/TD]
[TD]Mimi[/TD]
[TD]32[/TD]
[TD]N[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ashley[/TD]
[TD]21[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try this:

Excel 2012
DEFGHI
RankNameDaysIs 2/MHas Been 2/M
JohnYY
KimNN
AustinYY
JimmyNY
JuliaNY
EricNN
MorganNN
DanNY
MimiNN
AshleyYY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]64[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]25[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]32[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]21[/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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 />
 
Upvote 0
Another possible solution


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Rank​
[/td][td][/td][td]
Name​
[/td][td]
Days​
[/td][td]
Is 2/M​
[/td][td]
Has Been 2/M​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
2​
[/td][td][/td][td]
John​
[/td][td]
5​
[/td][td]
Y​
[/td][td]
Y​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
8​
[/td][td][/td][td]
Kim​
[/td][td]
18​
[/td][td]
N​
[/td][td]
N​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
3​
[/td][td][/td][td]
Austin​
[/td][td]
2​
[/td][td]
Y​
[/td][td]
Y​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
4​
[/td][td][/td][td]
Jimmy​
[/td][td]
64​
[/td][td]
N​
[/td][td]
Y​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
6​
[/td][td][/td][td]
Julia​
[/td][td]
8​
[/td][td]
N​
[/td][td]
Y​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
9​
[/td][td][/td][td]
Eric​
[/td][td]
14​
[/td][td]
N​
[/td][td]
N​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
10​
[/td][td][/td][td]
Morgan​
[/td][td]
1​
[/td][td]
N​
[/td][td]
N​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
5​
[/td][td][/td][td]
Dan​
[/td][td]
25​
[/td][td]
N​
[/td][td]
Y​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
7​
[/td][td][/td][td]
Mimi​
[/td][td]
32​
[/td][td]
N​
[/td][td]
N​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
1​
[/td][td][/td][td]
Ashley​
[/td][td]
21​
[/td][td]
Y​
[/td][td]
Y​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Ranking Array need to move as per the data
2
Replies
16
Views
514
sksanjeev786
S
D
  • Solved
Using COUNTIFS to Rank Within Nested IF, THEN
Replies
4
Views
563
diderooy
D
E
  • Solved
Ranking values (in descending order) with 1 criteria - BUT without skipping numbers
Replies
3
Views
311
Fluff
Fluff
C
  • Question Question
Determine Points per Selection Based on Rank and Number of Selections
Replies
1
Views
300
awoohaw
awoohaw
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
363
gtd526
gtd526

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top