# Unique ranking based on 3 criteria

#### David W 123

##### New Member
Hello and many thanks for reading this

I'm trying to create a formula for column E, which would rank based on 3 criteria (year, city name and sunshine days). The actual data is several hundred rows long. If 2 or more cities have the same number of days within a single year (eg Paris and London in 2023), I would want a unique rank - perhaps based by alphabetical order of the city name but not fussed.

Hoping someone can help

Thanks,
David

### Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### StephenCrump

##### MrExcel MVP
ABCD
1YearCityDaysRank
22020Paris1003
32020London864
42020Rome1701
52020New York1502
62021Paris1053
72021London904
82021Rome1801
92021New York1652
102022Paris1063
112022London924
122022Rome1852
132022New York1901
142023Paris1023
152023London1024
162023Rome2001
172023New York1802
Sheet1
Cell Formulas
RangeFormula
D2:D17D2=COUNTIFS(A\$2:A\$17,A2,C\$2:C\$17,">"&C2)+COUNTIFS(A\$2:A2,A2,C\$2:C2,C2)

If you want tiebreakers based on city alphabetical order, it's probably easiest if you sort by column A, then B, and use the same formula.

#### Marcelo Branco

##### MrExcel MVP
To take into account alphabetical order as tie-breaker, maybe

D2 copied down
=COUNTIFS(A\$2:A\$17,A2,C\$2:C\$17,">"&C2)+COUNTIFS(A\$2:A\$17,A2,C\$2:C\$17,C2,B\$2:B\$17,"<"&B2)+1

M.

#### David W 123

##### New Member
Excellent - thank you so much - this worked great

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Thanks for the feedback.

M.

Replies
9
Views
155
Replies
1
Views
307
Replies
1
Views
434
Replies
6
Views
128
Replies
20
Views
349

Threads
1,129,931
Messages
5,639,064
Members
417,067
Latest member
rohitbabshet

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

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