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
MirageSFAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
Sheet with rankings in columns J:K.
Rank-MirageSF.xlsx
0
 
Ejgil HedegaardCommented:
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.
0
 
MirageSFAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.