Rank - excluding criteria in the rank calculation

[Danizarc]

Hello,

I have a data set where one of the columns is the location and the other column associated with this location is the monetary movement. I am trying to Rank based on the monetary value.
However I also need to exclude certain locations, and therefore Rank based on monetary value for all locations except Germany and France. Please see my example below:

I want to exclude both Germany and France from the rank calculation Using the formula: =sumproduct(--("Germany"<>$A$2:$A$17),--("France"<>$A$2:$A$17),--($B2>$B$2:$B$17))+1 I achieve the below results, which include both France and Germany in the ranking A B C Country Claim Movement Rank Belgium <tbody> </tbody> 518,980 <tbody> </tbody> 4 Spain <tbody> </tbody> 64,296 <tbody> </tbody> 1 <tbody> </tbody> Germany <tbody> </tbody> 1,394,717 <tbody> </tbody> 10 <tbody> </tbody> Germany <tbody> </tbody> 415,797 <tbody> </tbody> 3 Netherlands <tbody> </tbody> 1,858,434 <tbody> </tbody> 11 Greece <tbody> </tbody> 983,577 <tbody> </tbody> 8 Italy <tbody> </tbody> 1,213,154 <tbody> </tbody> 9 Belgium <tbody> </tbody> 795,746 <tbody> </tbody> 5 Germany <tbody> </tbody> 901,470 <tbody> </tbody> 6 Greece <tbody> </tbody> 483,180 <tbody> </tbody> 3 Spain <tbody> </tbody> 903,838 <tbody> </tbody> 6 Belgium <tbody> </tbody> 950,230 <tbody> </tbody> 7 Ireland <tbody> </tbody> 1,838,103 <tbody> </tbody> 10 Greece <tbody> </tbody> 1,945,218 <tbody> </tbody> 12 France <tbody> </tbody> 386,153 <tbody> </tbody> 3 Belgium <tbody> </tbody> 374,301 <tbody> </tbody> 2 <tbody> </tbody> <tbody> </tbody> <colgroup><col><col></colgroup><tbody> </tbody> Any assistance would be greatly appreciated!! <colgroup><col><col></colgroup><tbody> </tbody> <colgroup><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[etaf]

does this work for you
=IF(AND(A2<>"france",A2<>"germany"),COUNTIFS(A$2:A$100,"<>france",A$2:A$100,"<>germany",B$2:B$100,"<"&B2)+1,"")

see example here
https://www.dropbox.com/s/vz0v9vt2wo7ug0c/Rank_ETAF.xlsx?dl=0

i will remove the file after a few days
as i still cannot get jeanie to work in excel 2013 - on windows 7 or 10

 

[Danizarc]

@ ETAF

Your formula has worked a charm!

I amended the "<" to ">" in order to rank the largest number as the first in the rank.

Much appreciated, you have helped me enormously!

Many Thanks

 

[etaf]

your welcome