# Conditional Rank in Descending Order

##### New Member
Right now i have the following in A1:B9

Code Name
D 12
D 23
F 45
E 32
F 67
F 90
S 19
S 26

and my conditional rank with formula copied down from C2 to C9 is:

=IF(A2="F",SUMPRODUCT((\$A\$1:\$A\$9=A2)*(B2>\$B\$1:\$B\$9))+1,"")

and I am getting this:

Code Name Rank By Letter F
D 12
D 23
F 45 1
E 32
F 67 2
F 90 3
S 19
S 26

Is there a way i could change the current ascending order to descending so i could get this:

Code Name Rank By Letter F
D 12
D 23
F 45 3
E 32
F 67 2
F 90 1
S 19
S 26

Thank you!

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### barry houdini

##### MrExcel MVP
Hello adjacent, welcome to MrExcel,

To reverse the order just change the > in the formula to a <

##### New Member
Oh ok Thank you!!!

And if i want to displayed the contents being ranked such that it looks like this:

Code Number Rank By Letter F Number with the Code F
D 12 45
D 23 67
F 45 3 90
E 32
F 67 2
F 90 1
S 19
S 26

How do i do so?

Last edited:

#### barry houdini

##### MrExcel MVP
In D2 you could use this formula

=IF(ROWS(D\$2:D2)>COUNTIF(A\$2:A\$9,"F"),"",INDEX(B\$2:B\$9,SMALL(IF(A\$2:A\$9="F",ROW(A\$2:A\$9)-ROW(A\$2)+1),ROWS(D\$2:D2))))

This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

Copy formula down the column

##### New Member
is there a way to autorun this array formula so my worksheet updates everytime my data changes? thank you!

1,101,810
Messages
5,483,042
Members
407,375
Latest member
achusp

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
• Please i need your help to create formula
I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
• Got error while adding column and filter
Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...