COUNTIF function to rank names marks different names the same rank

fufkin

New Member
Joined
Jan 20, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Attachments

  • Excel_Problem.png
    Excel_Problem.png
    11.8 KB · Views: 12

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi & welcome to MrExcel.
If you want to sort the names alphabetically, then you don't need to "rank" them, you can just use the inbuilt sort facility on the data tab.
 
Upvote 0
Hi & welcome to MrExcel.
If you want to sort the names alphabetically, then you don't need to "rank" them, you can just use the inbuilt sort facility on the data tab.
Hi Fluff. I understand that you can sort them like that, but I need to be able to do it through formulae.
I believe I have the correct formulae, but unsure why Excel gives the result it does??? For example, how can the COUNTIF function have 2 different input names and count the same number of results <= to them? If you look at the list Chris in B5 and Callum in B20 both have the result 2.
 
Upvote 0
Without having your data it's difficult to tell, but your formula works for me

Book1
AB
1
2
3
49
58Tom Pearce
61Bill Brewer
74Jan Stewer
86Peter Gurney
95Peter Davy
102Dan'l Whiddon
113Harry Hawke
127Tom Cobley
130And all
Data
Cell Formulas
RangeFormula
B4B4=COUNTA(B5:B100)
A5:A13A5=COUNTIF(OFFSET($B$5,0,0,$B$4,1),"<"&B5)
 
Upvote 0
You can use the XL2BB add-in which can be found here
 
Upvote 0
Ok, so the names in my list are from a large worksheet with multiple names on it and each may appear many times.

To get each unique name I use the array formula =INDEX(PEOPLE,MATCH(0,COUNTIF($B$4:$B4,PEOPLE),0)) in the range B5 to B20, where "PEOPLE" is a named range for the large list of names.

If I copy the names into a new spreadsheet and run the formula posted previously, then the COUNTIF function works as expected.
 
Upvote 0
I've now copied the names and pasted the values into the original worksheet and pointed the original formulae to the pasted names. It still doesn't calculate correctly.

I will try to upload in a moment.
 
Upvote 0
I'm not allowed to upload here due to some sensitive data, so I will have to try work out the problem another way.

I've now pasted the list of names in a new spreadsheet and copied all the formulae and everything works as expected. It's only in the original worksheet that I see the problem.
 
Upvote 0
Do you have a formula in B4? If so what is it?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top