Unique rank with multiple criteria

  • [TABLE="width: 881"]

    [tr]


    [td]

    Store Grade

    [/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    B

    [/td]


    [td]

    B

    [/td]


    [td]

    B

    [/td]


    [td]

    B

    [/td]


    [td]

    B

    [/td]


    [td]

    B

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    D

    [/td]


    [td]

    B

    [/td]


    [/tr]


    [tr]


    [td]

    Store Name

    [/td]


    [td]

    Store 1

    [/td]


    [td]

    Store 2

    [/td]


    [td]

    Store 3

    [/td]


    [td]

    Store 4

    [/td]


    [td]

    Store 5

    [/td]


    [td]

    Store 6

    [/td]


    [td]

    Store 7

    [/td]


    [td]

    Store 8

    [/td]


    [td]

    Store 9

    [/td]


    [td]

    Store 10

    [/td]


    [td]

    Store 11

    [/td]


    [td]

    Store 12

    [/td]


    [td]

    Store 13

    [/td]


    [/tr]


    [tr]


    [td]

    Units Sold

    [/td]


    [td]

    25

    [/td]


    [td]

    100

    [/td]


    [td]

    27

    [/td]


    [td]

    90

    [/td]


    [td]

    25

    [/td]


    [td]

    37

    [/td]


    [td]

    10

    [/td]


    [td]

    10

    [/td]


    [td]

    15

    [/td]


    [td]

    25

    [/td]


    [td]

    0

    [/td]


    [td]

    100

    [/td]


    [td]

    90

    [/td]


    [/tr]


    [tr]


    [td]

    Rank

    [/td]


    [td]

    7

    [/td]


    [td]

    1

    [/td]


    [td]

    6

    [/td]


    [td]

    3

    [/td]


    [td]

    8

    [/td]


    [td]

    5

    [/td]


    [td]

    11

    [/td]


    [td]

    12

    [/td]


    [td]

    10

    [/td]


    [td]

    9

    [/td]


    [td]

    13

    [/td]


    [td]

    2

    [/td]


    [td]

    4

    [/td]


    [/tr]


    [/TABLE]



    Tried getting the above outcome on ranking, with =RANK(B3,B3:N3), not getting desired results.
    The first criteria being the units sold in store and second criteria being the grading of the store.
    For example, Store2 and Store 12 sold 100 units each but Store2 being Grade"B" gets Rank 1 over Store 12.


    Any help will be appreciated

  • Re: Unique rank with multiple criteria


    Assuming your table starts at A1 (i.e. Store Grade is in A1), then try, in B4:


    [COLOR="#0000FF"]=RANK(B3,$B$3:$N$3)+SUMPRODUCT(--($B$3:$N$3=B3),--(B1>$B$1:$N$1))
    [/COLOR]
    copied across

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016