How do create Students Rank List Based on Pass candidate only

[Ganeszgk]

How do create Students Rank List in excel,Based on "Pass" candidate only To assume Rank,If "fail"candidates not assume that marks. I tried this formula =RANK(c6,$c$6:$c$41)
It's assumed all marks to getting Rank for pass & fail students. This is wrong, i want only Rank for pass students only not consider fail students. Please anybody help.
Example of my sheet:
Name M1 M2 M3 Total Result Rank
Aaa. 70. 30. 49. 125. Fail. 2
Bbb. 40 35 40. 115. Pass. 3
Ccc. 50 50 50. 150 Pass. 1

This is wrong, I don't want this method.

Example sheet:
Name M1 M2 M3 Total Result Rank
Aaa. 70. 30. 49. 125. Fail. --
Bbb. 40 35 40. 115. Pass. 2
Ccc. 50 50 50. 150 Pass. 1

This is correct, I want this method. Please help.
NOTE: Not assume fail student mark for ranking system.

 

[Guru008]

Hi,

There is no Rankif available in excel. Try using below sumproduct which works perfectly for your requirement

=1+SUMPRODUCT(($C$2:$C$12="pass")*($B$2:$B$12>B2))

I am able to respond in real time to you until it is solved

 

[Tetra201]

Is this what you need?

=IFERROR(1+COUNTIFS($F$2:$F$100,"Pass",IF($F$2:$F$100="Pass",$E$2:$E$100,0),">"&E2),"--")

 

[Ganeszgk]

Name M1 M2 M3 Total Result Rank
Aaa. 70. 30. 49. 125. Fail. --
Bbb. 40 35 40. 115. Pass. 2
Ccc. 50 50 50. 150 Pass. 1

This is correct..

THANK YOU VERY MUCH Tetra201 FOR YOUR VALUABLE TIME... I FOLLOWED YOUR FORMULA AND I GOT EXACT RANK FOR PASS STUDENTS ONLY...

Tetra201 has just replied to a thread you have subscribed to entitled - How do create Students Rank List Based on Pass candidate only - in the Excel Questions forum of MrExcel Message Board.

 

[Aladin Akyurek]

In G2 enter and copy down:

=IF($F2="pass",SUMPRODUCT(--($F$2:$F$5="pass"),--($E$2:$E$5>$E2))+1,"--")

Adjust the ranges to suit.

 

[Tetra201]

@Ganeszgk:

You are welcome.