Unique ranking based on 3 criteria

David W 123

New Member
Joined
May 14, 2020
Messages
9
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.JPG
 

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
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 23, 2010
Messages
16,524
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
May 14, 2020
Messages
9
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Excellent - thank you so much - this worked great :)
 

Watch MrExcel Video

Forum statistics

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

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
Top