dynamic ranking based on filters

[sabha]

Hi, I need my ranking to change based on the selected filters. Below is what it looks like right now:

RANK PLACE REVENUE
1 AMS 11
6 APJ 4
5 EMEA 5
4 US 6
3 CAN 7
2 BRAZIL 10

As of now, the rank column is ranking based on my range. However, if I filter only on US, CAN, and Brazil, then I want my ranking to be:

RANK PLACE REVENUE
3 US 6
2 CAN 7
1 BRAZIL 10

Is there a way to do that on excel? Would appreciate your help. I need to do this because I am trying to rank the top countries based on the filters I select so if i only want to see the Americas countries then I only want to rank US, CAN, and Brazil. Thank you for your help!

 

[Eric W]

Assuming that you're using the regular Excel filter tool, try putting this in A2 and copy down:

=SUM(IF((SUBTOTAL(102,OFFSET($C$2,ROW($C$2:$C$7)-ROW($C$2),0))>0)*($C$2:$C$7>C2),1))+1
confirmed with Control+Shift+Enter.