# RANK Solution needed please :)

#### Bennets04

##### Board Regular
Hello All,

Im sure you will be able able to help with the following question:

I am trying to RANK a list of data but where the number maybe the same how can i get the formula to not have duplicates... So far example below i want James to be ranked 4th and Sally 5th even though they both had the same score

 Name Score Rank Scott 100 1 James 59 4 Sally 59 4 Beth 99 2 David 60 3

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

=RANK.EQ(C3,\$C\$3:\$C\$7,0)

Many thanks
Steve

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### barry houdini

##### MrExcel MVP
Hello Steve, you can add a COUNTIF fuunction to get the answer you want, try this formula in D3 copied down

=RANK.EQ(C3,C\$3:C\$7)+COUNTIF(C\$3:C3,C3)-1

#### Matt Rogers

##### Well-known Member
Wrong formula ...

Last edited:

#### Bennets04

##### Board Regular

Hello Steve, you can add a COUNTIF fuunction to get the answer you want, try this formula in D3 copied down

=RANK.EQ(C3,C\$3:C\$7)+COUNTIF(C\$3:C3,C3)-1

This didnt work as i was hoping to pull the formula down to around 200 ranked associates

Im after a formula that will count the ranks all the way down then know if the same rank was already highlighted above it will add a number then so on.

Hope that helps

Last edited:

#### Bennets04

##### Board Regular
Just to let you i have managed to fix this

Found it on Excelisfun youtube channel

Many thanks for your thoughts all

Thanks
Steve

#### Matty

##### Well-known Member

barry houdini's formula should work. Perhaps the wrong ranges were used. See below:

Excel 2012
ABC
1NameScoreRank
2Scott1001
3James594
4Sally595
5Beth992
6David603

<tbody>
</tbody>
Sheet2
Formula in C2 is:

Code:
``=RANK.EQ(B2,B\$2:B\$6)+COUNTIF(B\$2:B2,B2)-1``

Which can then be copied down.

Matty

#### barry houdini

##### MrExcel MVP
Im after a formula that will count the ranks all the way down then know if the same rank was already highlighted above it will add a number then so on.

That's what the formula does, Steve - if you have 200 values to rank then you just need to change the cell references appropriately, e.g. if data to rank is in C3:C202 then the formula in row 3 will be this:

=RANK.EQ(C3,C\$3:C\$202)+COUNTIF(C\$3:C3,C3)-1

copy that down and you'll get the ranks you want

Which formula did you use?

#### Bennets04

##### Board Regular
Thank you for the replies... i managed it with the following

=RANK.EQ(C2,\$C\$2:\$C\$200,0)+COUNTIF(\$C\$1:C1,C2)

Just dragged it down and worked a treat

Replies
43
Views
2K
Replies
0
Views
37
Replies
4
Views
322
Replies
11
Views
634
Replies
1
Views
237

1,118,877
Messages
5,574,770
Members
412,617
Latest member
mlharris