Need help to rank without any duplicates

[hawkinsr86]

Hi guys,

I need help with the below. Basically i need to rank each city in each country according to how many units were sold. For example London is ranked first in the UK because it has the most units in the UK. Guangzhou is also ranked first because it has the most units in china. Similarly with Paris. However, Manchester and Glasgow have the same number of units. instead of them both being ranked second i want Glasgow to be second and manchester third because glasgow has the most revenue. Similarly i want Beijing ranked second and jinling 3rd because beijing has the most revenue.

Country City State Units Revenue Rank I want
United Kingdom London 50 6164 1
China Guangzhou 48 6365 1
France Paris 47 7873 1
United Kingdom Manchester 46 8369 3
United Kingdom Glasgow 46 9796 2
United Kingdom Edinburgh 45 5420 4
United Kingdom Birmingham 44 9905 5
United States Buffalo NY 14 5427 1
China Beijing 10 6614 2
China Jinling 10 5400 3

Thanks for you help.

Thanks

[jake.masters]

Depending on how many countries you are dealing with, I would run a sort where the first level is country, the next is number of units, and the third is revenue. You could then add the rankings by dragging down numbers. Due to the sort, everything should be in the correct order and all you have to do is add a rank.

If you posted a sample worksheet, I believe people would be more willing to help out.

[daddylonglegs]

Assuming countries in A2:A100, Units in D2:D100 and Revenue in E2:E100 then try this formula in row 2 copied down for the correct ranks:

=COUNTIFS(A$2:A$100,A2,D$2:D$100,">"&D2)+COUNTIFS(A$2:A$100,A2,D$2:D$100,D2,E$2:E$100,">"&E2)+1

data doesn't need to be sorted