# Formula to determine rank, given set of values

#### kwp004

##### Board Regular
In column A, I have a set of unique values, and in column B, I have another set of unique values. I'm trying to develop a formula that would tell me the rank of the most common value in column B, given the value in column A.

So for example, for AA, it'd be #1 Dog, #2 Cat, #3 Bird.

 AA Cat AA Cat AA Dog AA Dog AA Dog AA Dog AA Bird BB Cat BB Cat BB Bird

I've been trying to use a combination of countifs and mode, without much success.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### kwp004

##### Board Regular
The problem with that approach is that I don't have numbers in my dataset, it's all non-numeric values (similar to the example I provided).

#### shaowu459

##### Well-known Member
What is your expected result?power query may solve this very easily.

#### shaowu459

##### Well-known Member

Maybe you need something like this? This is done by Power Query and I need the exact layout of your expected result to modify the code.

if you only need the sequence of the items, Pivot Table will do:

#### CA_Punit

##### Well-known Member
This can be done using formula also
However if the count of Two criteria are same then it will consider the criteria selected to be Rank greater than the others

Book1
ABCDEFGH
1NameHelper
2AACat0Rank
3AACat0BBBird1
6BBCat3
7BBBird3
8BBCat0
9BBCat0
10BBBird0
11BBBird0
12
Sheet2
Cell Formulas
RangeFormula
H3H3=RANK.EQ(COUNTIFS(A2:A11,F3,B2:B11,G3),C2:C11,0)
C2:C11C2=IF(COUNTIFS(\$A\$1:A2,\$F\$3,\$B\$1:\$B2,B2)=1,SUM((\$A\$2:\$A\$11=\$F\$3)*(\$B\$2:\$B\$11=B2)*IF(\$A\$2:\$A\$11=\$F\$3,(ROW(\$A\$2:\$A\$11)-ROW(\$A\$2)+1)^0)),0)

#### kwp004

##### Board Regular
Perfect, thank you!

Replies
9
Views
103
Replies
21
Views
362
Replies
5
Views
348
Replies
4
Views
225
Replies
5
Views
264

1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

### 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