# How do create Students Rank List Based on Pass candidate only

#### Ganeszgk

##### New Member
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

NOTE: Not assume fail student mark for ranking system.

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### Guru008

##### Board Regular
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

##### MrExcel MVP
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

##### New Member
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.

##### MrExcel MVP
In G2 enter and copy down:

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

@Ganeszgk:

You are welcome.

Replies
5
Views
158
Replies
1
Views
2K
Replies
1
Views
77
Replies
18
Views
177
Replies
3
Views
234

1,191,723
Messages
5,988,307
Members
440,148
Latest member
sandy123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back