Conditional Ranking

[mang121]

Hi. I am trying to rank a range of cells base on certain criteria:

Criteria 1 - No ties using column G as the tie breaker.
Criteria 2 - Do not rank the columns that state "Not Qualified"

Here is my current formula:

RANK(AJ9,AJ$9:AJ$18)+SUMPRODUCT(--($AJ$9:$AJ$18=AJ9),--($G$9:$G$18>G9))

G AJ AK AL

149854.93 34 34 3
349614.25 35 Not Qualified 2
139012.59 36 36 1





Any thoughts on how to rank column AL based on point values in AJ1:AJ3 and continuing to use Column G as a tie breaker while not including value AK2 would be much appreciated.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Question: what would be the expected results fin the case below (note that AJ3 = AJ4)?


[TABLE="class: grid"]
<tbody>[TR]
[TD]

G​

[/TD]
[TD]

AJ​

[/TD]
[TD]

AK​

[/TD]
[TD]

AL​

[/TD]
[/TR]
[TR]
[TD]

149854,93​

[/TD]
[TD]

34​

[/TD]
[TD]

34​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR]
[TD]

349614,25​

[/TD]
[TD]

35​

[/TD]
[TD]

Not Qualified​

[/TD]
[TD]

?
​

[/TD]
[/TR]
[TR]
[TD]

139012,59​

[/TD]
[TD]

36​

[/TD]
[TD]

36​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR]
[TD]

120000,00​

[/TD]
[TD]

36​

[/TD]
[TD]

36​

[/TD]
[TD]

?​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[mang121]

The expected result in AL would be a rank of:
3
4
1
2

I was able to get it to not rank the "Not Qualified" but lost my tie breaker with this:
IF(NOT($J9>=100%),"Not Qualified",IF(OR(NOT($P9>=100%),NOT($V9>=100%)),"Not Qualified",SUMPRODUCT(--($P$9:$P$18>=100%),--($V$9:$V$18>=100%),--($AJ$9:$AJ$18>$AJ9))+1))

 

[Marcelo Branco]

See if this does what you need

AL1 copied down
=IF(ISNUMBER(AK1),COUNTIFS($AJ$1:$AJ$4,">"&AJ1,AK$1:AK$4,"<>Not Qualified")+1+COUNTIFS(AJ$1:AJ$4,AJ1,AK$1:AK$4,"<>Not Qualified",G$1:G$4,">"&G1),"")

Hope this helps

M.

 

[Marcelo Branco]

The formula above assumes the data in rows 1:4. It seems your data are in rows 9:18, so adjust the ranges according to your case.

M.

 

[mang121]

Perfect!! You're a wizard. Works great!

 

[Marcelo Branco]

Glad it worked

M.