asked on

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

Ejgil Hedegaard

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,"")

Open in new window

I don't understand how the values in column H should be used in the ranking, please explain.

MirageSF

ASKER

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

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial