How to Rank based on text in another cell

[mlbdc2012]

Does anyone know how to rank values in one column based on criteria in another column?

For example: How do I rank Cells in Column A that only contain the text "AL" in column B? Thanks!

COL...A....B
ROW
1......5....NL
2......6....AL
3......7....AL
4......8....NL

[FDibbins]

Not sure what you mean by rank? Do you want this put somewhere else? I presume you have tried to SORT, and it didnt give you wnat you wanted?

[benishiryo]

hi mlbdc. in descending order:
=IF(B1="AL",COUNTIF(A:A,">"&A1),"")

ascending:
=IF(B1="AL",COUNTIF(A:A,"<"&A1),"")

Edit: no.... probably need to modify. should be:
=IF(B1="AL",COUNTIFS(A:A,">"&A1,B:B,"AL")+1,"")

=IF(B1="AL",COUNTIFS(A:A,"<"&A1,B:B,"AL")+1,"")

[martindwilson]

try
=SUMPRODUCT(($B$1:$B$10=B1)*($A$1:$A$10>A1))+1
or just to rank al
=IF(B1<>"al","",SUMPRODUCT(($B$1:$B$10="al")*($A$1:$A$10>A1))+1)

[mlbdc2012]

The formula works great for any cell with a value in it but gives a 1 to all the cells that don't have a value in it... Any ideas how to get those ones to disappear while still having the formula inserted in that particular cell?

[martindwilson]

=if(or(a1="",b1=""),"",sumproduct(($b$1:$b$10=b1)*($a$1:$a$10>a1))+1)