Unique ranking based on 3 criteria

[David W 123]

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

 

[StephenCrump]

	A	B	C	D
1	Year	City	Days	Rank
2	2020	Paris	100	3
3	2020	London	86	4
4	2020	Rome	170	1
5	2020	New York	150	2
6	2021	Paris	105	3
7	2021	London	90	4
8	2021	Rome	180	1
9	2021	New York	165	2
10	2022	Paris	106	3
11	2022	London	92	4
12	2022	Rome	185	2
13	2022	New York	190	1
14	2023	Paris	102	3
15	2023	London	102	4
16	2023	Rome	200	1
17	2023	New York	180	2
Sheet1

Cell Formulas
Range	Formula
D2:D17	D2	=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]

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]

Excellent - thank you so much - this worked great

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.