# RANK with Multiple Criteria

Hi,

How do I RANK with multiple criteria, i.e. if a certain column contains A then RANK
A      B      C      D      E      F      G      H      I                  EXPECTED

RANK                  A                        500                  2
RANK                  A                        700                  1
RANK                  B                        900
RANK                  A                        150                  3
RANK                  C                        17
RANK                  D                        5
RANK                  B                        150
RANK                  B                        5
RANK                  A                        12                  4
RANK                  D                        12

Then how would I add additional ranges to include in the rank at a later date, so if column H was to contain 10 5 9 8 etc, to include that when considering the RANK order
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
With A, B, C, D in column C, and the values in column E, you can use this formula
``````=IF(C1="A",COUNTIFS(\$C\$1:\$C\$10,"A",\$E\$1:\$E\$10,">"&E1)+1,"")
``````
I don't understand how the values in column H should be used in the ranking, please explain.
0
Author Commented:
Hi,

I have attached an example, I actually used =F8+G8/10000+1000*(-CODE(E8)+87) to generate unique codes which I can then autofilter and get exact results I need (kinda)...

Although it will sort exactly as required, I need to limit it to 10 of each category and that's where I am now stuck, I want it to show NO more than ten each of ES,LS,NS and WS.  How to?

Thanks
Rank.xlsx
0
Commented:
Sheet with rankings in columns J:K.
Rank-MirageSF.xlsx
0

Experts Exchange Solution brought to you by