Rank using 3 criteria

[kishen]

Hi there. I've been struggling with a problem for almost a week now. I'm trying to assign a rank score to some data based on 3 criteria. The end result should be in a single cell (in fact, I'm using nested if statements to add different combinations, but this is the toughest one).

Criterion 1: Global, Regional or Local (column F)
Criterion 2: Yes or No (column I)
Criterion 3: A score between 1 and 100 (column M)

It works fine when I try to use Criteria 2 and 3 or Criteria 1 and 3, but I'm struggling with all 3 in the same formula.

C1+3 formula is:
COUNTIF($F$3:$F$22,"<"&$F3)+COUNTIFS($F$3:$F$22,$F3,$M$3:$M$22,">"&$M3)+1

C2+3 formula is: COUNTIF($I$3:$I$22,">"&$I3)+COUNTIFS($I$3:$I$22,$I3,$M$3:$M$22,">"&$M3)+1

C1+2+3... need help here.

Thank you

 

[RasGhul]

Try

=SUMPRODUCT((COUNTIF($F$3:$F$22,"<"&$F3)+(COUNTIF($I$3:$I$22,">"&$I3)+(COUNTIFS($F$3:$F$22,$F3,$M$3:$M$22,">"&$M3)+1))))

 

[kishen]

Try

=SUMPRODUCT((COUNTIF($F$3:$F$22,"<"&$F3)+(COUNTIF($I$3:$I$22,">"&$I3)+(COUNTIFS($F$3:$F$22,$F3,$M$3:$M$22,">"&$M3)+1))))

I tried that myself earlier... it doesn't work as needed. The rank has duplicate numbers and skips a few numbers as well. Looking at the scores, that shouldn't be the case.

 

[Marcelo Branco]

Could you post an example? A small data sample, criteria, and expected results would be helpful.

To post a data sample see section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[navic]

Hi there. I've been struggling with a problem for almost a week now....

Cross posting https://chandoo.org/forum/threads/ranking-by-3-or-more-criteria.42258/