How to Rank a Grouped list using excel's =RANK function

Hello everybody,

I have a grouped report, by salesperson then by customer accounts. For each salesperson's list of customer's accounts, i'd like to assign Ranks based on their sales using the RANK function in Excel…. HOWEVER, the cell reference range for the rank is dynamic as it creates rows of accounts for each salesperson. the cell range should always be the first row where the account starts to the last account row per salesperson… Any suggestions would be appreciated!

Thanks