CODEWITHSUNDEEP
excelexcel-formulaformulas
Deepak Ved
Deepak Ved

Reputation: 61

How to Rank using Rank formula basis different criteria in excel

I have below table

Month   LoB Score   Rank
Jan     A   1   
Jan     B   2   
Feb     B   1   
Feb     B   2   
Jan     A   2   
Mar     C   1   
Feb     A   3   
Jan     A   3   
Mar     C   2   
Mar     A   1   
Mar     C   3

I want to Rank the scores basis Month and LoB. For ex in Jan for A whatever is highest will get Rank 1. Similarly in Jan for LoB B whatever is highest will get Rank 1. I understand that Index and Row formula are to be used in conjunction with Rank.eq but i am unable to put it together at all. I would appreciate any help on this. Thank you

Upvotes: 1

Views: 384

Answers (2)

Malik Muhammad Siraj
Malik Muhammad Siraj

Reputation: 1

AoA / Good morning by using Rank formula problems face in correct position.=RANK(K5,K5:K34)

Marks     Position    total marks 350
obtained
    290     29
    346     9 (student obtained 346 marks how he have 9th position he must be 4th position)
    250     30
    343     20
    345         13
    342     21
    334     26
    346     9
    345     13
    346     9
    346     9
    348     5
    350     1
    349     3
    335     24
    345     13
    335     24
    348     5
    339     22
    295     28
    350     1
    345     13
    348     5
    344     18
    345     13
    338     23
    347     2
    349     3
    297     27

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Assuming Row1 is the header row and actual data lies in the range A2:C11, then try this...

In D2

=SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=B2)*($C$2:$C$11>C2))+1

and copy it down. enter image description here

Upvotes: 2

Related Questions