Formula to rank persons by state in each country (2 groups/criteria)

Hello,

I would like to rank a list of persons (represented by their ID) at State/Province level based on their worldrank value (the lower the value the higher the rank).
For example Persons in the united states ranked by each state based on their worldrank; it is a sub ranking by state/province. I could rank based on the state/province name only and forget about the country but the issue is that in some countries there are states/provinces with the same name. For example both Argentina and Colombia have a province called Cordoba. So if I apply the usual ranking formula based on one criteria/group the result is that it considers cordoba in the same state ranking and does not create one state/province ranking for the two different countries.
What formula should I use to create different state/province rankings based first on the country and then the state/province (not just one group/criteria but two).

I attach the worksheet hoping it will help understand better the issue.

Thanks for any help!

world33

Hi Modytrane,

The states were already sorted. How would that make a difference?

ty

I think you need to provide what the format of your output is in the model answer. Modytrane has provided an answer that works. However as you have not given a sample answer it is probably different to what you are after. why not reduce the data to a few lines as well to make the task easier

Thanks for the feedback. I have updated the worksheet to highlight the issue.
By using the following formula

=1+SUMPRODUCT(($C$2:$C$5000=C2)*($D$2:$D$5000<D2))

Which does not include the Country criteria it happens that states/provinces with the same name in different countries are ranked in the same sub-ranking.
How can I modify the formula so that I can add an additional criteria based on the country to prevent this problem?

I hope it is clearer now.

TY

I wouldn't do things your way, however if you wanted to expand it

=1+SUMPRODUCT(($B$2:$B$5000=B2)*($C$2:$C$5000=C2)*($D$2:$D$5000<D2))