{RankIfs} Rank a column A based on values in Column B

[Surobhi]

Hi Guys,

Unfortunately there is no RankIF function in Excel but in reality this is all I need now.
Example Worksheet attached.
Col A - Class A,B,C
Col B - Marks obtained by students
In Col C - I need to rank the students based on the class. Which means, that in this column I should be able to see who stood 1st, 2nd, 3rd in each of the class A,B, C and so on using a single formulae
Help solicited.

Thanks,
Surobhi

 

[NARAYANK991]

Hi Surobhi ,

Instead of using the RANK function , can we use the LARGE function ?

See the file.

Narayan

 

[Surobhi]

Thanks Narayan,
.
I actually want the ranks to be displayed in Col C.
Something like this.
Class Marks RANK
A 6 5
B 56 4
C 87 1
B 88 3
B 10 8
B 40 5
C 61 6
A 50 2
B 21 7
A 15 4
B 95 1
C 65 3
C 62 4
A 29 3
A 83 1
B 95 1
B 32 6
C 62 4
C 74 2

 

[XOR LX]

Hi.

Maybe, in C2:

=1+COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,">"&B2)

Copy down as required.

Regards

 

[Surobhi]

That works, thanks a ton.

 

[XOR LX]

You're welcome!

 

[PaulF]

Hi.

Maybe, in C2:

=1+COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,">"&B2)

Copy down as required.

Regards

I would have never put that together, but I'm glad you did... We can all learn this way

 

[XOR LX]

I would have never put that together, but I'm glad you did... We can all learn this way

Very kind of you to say so! Cheers!

 

[Syedali]

Hi.

Maybe, in C2:

=1+COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,">"&B2)

Copy down as required.

Regards

Great...

 

[XOR LX]

Great...

Thanks!