Conditional Ranking with large data set

[erika791]

Hi Everyone,

I have a large data set that includes multiple columns (Country, Name of Store, year to date sales, growth, etc.) and multiple rows (Australia, China, Hong Kong, etc.). I need to rank each store's sales but only within their country. However, I want the formula to expand all of the countries so I don't have to redo the formula every time a new store is open within a particular country. I've been trying to find a way to automate this because I will have to do this exercise every month.

So far, I've tried SUMPRODUCT per other guidance with the below formula:
=1+SUM(IF($A$6:$A$1800=A6,IF((G6+ROW(G6)*0.00001)>($G$6:$G$1800+ROW($G$6:$G$1800)*0.00001),1,0),0))

However, it still gives me rankings for all countries. For example, if I am trying to rank the stores in China, it will still pop-up ranks for the stores in Australia. Ideally, I can use a formula that will just give me blanks when the rank formula is on the country that I am currently not ranking.

Is there a "rankif" type formular that I could use for this?