COUNTIF function to rank names marks different names the same rank

[fufkin]

Hello. Please help, as I'm losing the will to live!
I have a list of unique peoples names starting in cell B5 to B20, which I want to rank so that I can sort alphabetically. I have a count of names in cell B4.
I've tried several formulas and I get the same result each time. Excel ranks 2 different names the same number.
I've tried the following formulae:

In A5 I have:
=COUNTIF(OFFSET($B$5,0,0,$B$4,1),"<"&B5)

In D5 I have:
=IF(B5<>"",SUMPRODUCT((B5>=OFFSET($B$5,0,0,$B$4,1))+0),"")

I've attached a screenshot of the result. I've obfuscated part of the names for obvious reasons.

 

[fufkin]

Hi Fluff,

Bingo! Yes I do and it is what is causing the problem. I use it to count the number of unique entries. The formula is =SUMPRODUCT((PEOPLE <>"")/COUNTIF(PEOPLE ,PEOPLE ))

I'm not sure where that formula came from, online search somewhere.

I will try to find an alternative.

 

[fufkin]

Hi Fluff,
Yes I do. The function is =SUMPRODUCT((Employees<>"")/COUNTIF(Employees,Employees & ""))
If I manually type the answer then the list works as expected.
So I guessed that maybe that function isn't outputting an integer, so I rewrote it to =INT(SUMPRODUCT((Employees<>"")/COUNTIF(Employees,Employees & "")))

And BINGO!!! everything works as expected.

Thank you for your help, it is very much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback