I am attempting to set up a ranking, however I have run into a slight problem. Not all staff will be included in the ranking. Obviously anyone without sales volume will be excluded (I have an If statement for this). In addition, only staff associated with certain location numbers (location is column A) will be ranked. I have these locations (there are 4 of them) set up in a named range (Tier_1). The named range is listed in column N. My formula is currently:

=IF($C2="","-",RANK($C2,$C$2:$C$26,0)+COUNTIF($C$2:$C2,$C2)-1)

This formula works, however it includes ALL staff. Column A in the attached example shows the location number. Only 4 locations qualify for a bonus, based on ranking, which is why I need to exclude all staff. I have played around with the formula a bit, but continue getting errors.