Duplicate ranks while ranking with Countif for different numbers

[sgr791990]

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

 

[Eric W]

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
	E	F	G	H
1	Values	Countif	Rank	Sumproduct
2	48737.76	2	2	2
3	36113.11	3	3	3
4	54287.76	1	1	1
Sheet5

Cell Formulas
Range	Formula
F2:F4	F2	=COUNTIF($E$2:$E$4,">"&E2)+1
G2:G4	G2	=RANK.EQ(E2,$E$2:$E$4)
H2:H4	H2	=SUMPRODUCT(--(ROUND($E$2:$E$4,2)>ROUND(E2,2)))+1

 

[sgr791990]

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
	E	F	G	H
1	Values	Countif	Rank	Sumproduct
2	48737.76	2	2	2
3	36113.11	3	3	3
4	54287.76	1	1	1
Sheet5

Cell Formulas
Range	Formula
F2:F4	F2	=COUNTIF($E$2:$E$4,">"&E2)+1
G2:G4	G2	=RANK.EQ(E2,$E$2:$E$4)
H2:H4	H2	=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]

Happy to help! Thanks for the update.