Conditional Rank in Descending Order

[adjacent]

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!

 

[barry houdini]

Hello adjacent, welcome to MrExcel,

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

 

[adjacent]

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?

 

[barry houdini]

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

 

[adjacent]

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