# Duplicate ranks while ranking with Countif for different numbers

#### sgr791990

##### New Member
Hi All,

I am trying to rank a set of numbers with countif but it gives me the same rank for different numbers while rank function works fine as shown below:

Here are the formulae used for the first row. The same formulae have been dragged down to rest of the rows.
=COUNTIF(\$E\$2:\$E\$4,">"&E2)+1
=RANK.EQ(E2,\$E\$2:\$E\$4)

Has anyone faced this issue before or has any ideas how to solve this?
I need to use countifs because i am ranking based on multiple fields in my original dataset and cannot use the rank function.

Any help is appreciated.

Regards,
Syed

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Eric W

##### MrExcel MVP
Welcome to MrExcel!

It actually works for me (see below). This appears to be a kind of rounding error. The way Excel stores numbers causes some issues when you have decimals. It is a bit rare when comparing a cell to itself, but it can happen. You can try to use SUMPRODUCT instead, see column H.

Book1
EFGH
1ValuesCountifRankSumproduct
248737.76222
336113.11333
454287.76111
Sheet5
Cell Formulas
RangeFormula
F2:F4F2=COUNTIF(\$E\$2:\$E\$4,">"&E2)+1
G2:G4G2=RANK.EQ(E2,\$E\$2:\$E\$4)
H2:H4H2=SUMPRODUCT(--(ROUND(\$E\$2:\$E\$4,2)>ROUND(E2,2)))+1

#### sgr791990

##### New Member
Welcome to MrExcel!

It actually works for me (see below). This appears to be a kind of rounding error. The way Excel stores numbers causes some issues when you have decimals. It is a bit rare when comparing a cell to itself, but it can happen. You can try to use SUMPRODUCT instead, see column H.

Book1
EFGH
1ValuesCountifRankSumproduct
248737.76222
336113.11333
454287.76111
Sheet5
Cell Formulas
RangeFormula
F2:F4F2=COUNTIF(\$E\$2:\$E\$4,">"&E2)+1
G2:G4G2=RANK.EQ(E2,\$E\$2:\$E\$4)
H2:H4H2=SUMPRODUCT(--(ROUND(\$E\$2:\$E\$4,2)>ROUND(E2,2)))+1
Thanks. I just used the round function with countif and it works. Its weird but it works.
Here is what i used: =COUNTIF(\$E\$2:\$E\$4,">"&ROUND(E2,2))+COUNTIF(E\$2:E2,ROUND(E2,2))

Thanks again. you saved me some urgent manual work

#### Eric W

##### MrExcel MVP
Happy to help! Thanks for the update.

Replies
1
Views
160
Replies
3
Views
464
Replies
11
Views
449
Replies
11
Views
481
Replies
0
Views
89

1,181,892
Messages
5,932,669
Members
436,850
Latest member
Jasperlee93

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back