Rank with criteria

[ORoxo]

Hey, guys,
I'm really going crazy with this as I have not been able to figure it out for a couple of days.
Let's take the following picture as an example:

I'm basically looking for a formula on input on column D which does the exact same thing but with one exception. Let's say we on row 21 we have data exactly as we do on row 19. In that case, I want the formula to return the "2" as it did on row 19.

Any idea how this can be achieved?

 

[BarryL]

So you want to match columns A, B & C and lookup what their score was in column D? Could you try the below entered in D21

=IF(SUMPRODUCT((A21=$A$1:A20)*(B21=$B$1:B20)*(C21=$C$1:C20))=1,SUMPRODUCT((A21=$A$1:A20)*(B21=$B$1:B20)*(C21=$C$1:C20),$D$1:D20),"")

 

[ORoxo]

So you want to match columns A, B & C and lookup what their score was in column D? Could you try the below entered in D21

=IF(SUMPRODUCT((A21=$A$1:A20)*(B21=$B$1:B20)*(C21=$C$1:C20))=1,SUMPRODUCT((A21=$A$1:A20)*(B21=$B$1:B20)*(C21=$C$1:C20),$D$1:D20),"")

Unfortunately, it didn't work, Barry

 

[Aladin Akyurek]

Care to post your sample in an Excel readbale form?

 

[ORoxo]

Care to post your sample in an Excel readbale form?

There you go, Aladin

https://ufile.io/msfsy

 

[Aladin Akyurek]

There you go, Aladin

https://ufile.io/msfsy

In D2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($C$2:$C$21=C2,IF($B$2:$B$21>$B2,$B$2:$B$21)),$B$2:$B$21),1))+1

 

[ORoxo]

In D2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($C$2:$C$21=C2,IF($B$2:$B$21>$B2,$B$2:$B$21)),$B$2:$B$21),1))+1

We are soooo soooo close...

The only problem with this is if there are two distinct students in the same department with the same grade. Assuming there are two students with the same top grade, in that case, the formula should rank one of them as 1 and the other one as 2. However, its currently ranking both as 1.

Note to self: I reallt need to study FREQUENCY function

 

[Aladin Akyurek]

We are soooo soooo close...

The only problem with this is if there are two distinct students in the same department with the same grade. Assuming there are two students with the same top grade, in that case, the formula should rank one of them as 1 and the other one as 2. However, its currently ranking both as 1.

Note to self: I reallt need to study FREQUENCY function

Try to create a sample including this new condition along with the expected results (manually).

 

[ORoxo]

Try to create a sample including this new condition along with the expected results (manually).

I think this pretty much sums it up

https://ufile.io/0evq6

Thanks once again for your amazing help!

 

[ORoxo]

Bump