-1

I am trying to rank the clients based on fees. As I've shown in below table, in column A I have the country and column B the client name. So I want to find the Ranking in column D based on fee has been charged in column C. I have over 20K clients and I have tried to do it in Pivot table but it does not make a clear ranking for me because of the two criteria listing.

Which combination of formulas would solve this problem?

My table;

    A          B           C         D
1   Country   N-Client    Fees      Rank
2   Belgium   A Company   $720       
3   Canada    A Company   $210       
4   Belgium   B Company   $1300      
5   Canada    A Company   $500       
6   Canada    C Company   $600       

Thank you

  • 2
    I'm voting to close this question as off-topic because questions must demonstrate a reasonable amount of research & understanding of the problem being solved. Please edit to either a) clearly describe your problem and the research done so far to solve it or b) include attempted solutions plus why they didn't work. In either case, be sure your expected results are clearly presented. – Tetsujin Jan 11 at 8:51
  • Hi @Tetsujin I am sorry, I've just elaborated the request. – broiling Jan 11 at 9:40
  • Is there any reason why you can't simply sort the table on the values in C? – cybernetic.nomad Jan 11 at 18:07
  • Did you try the RANK function? – fixer1234 Jan 12 at 16:55
0

I solved this by setting up a helper column then a rank column. Set helper column row 2 to the following then copy down the sheet so the B2 at the end gets incremented to b3,b4 etc...:

=SUMIFS(C:C,A:A,A2,B:B,B2)

Then set a rank column (starting with row 2 and copying down) with the following replacing D:D with the column you choose above for the helper subtotal column:

=INDIRECT(ADDRESS(MATCH(LARGE(D:D,ROW()-1),D:D,0),2))&" "&INDIRECT(ADDRESS(MATCH(LARGE(D:D,ROW()-1),D:D,0),1))

You should end up with your ranked column with the highest paying company+country at the top and working down.

Edit: hmm this does result in duplicate entries. You could copy your rank column, paste(special)-values somewhere then do a duplicate removal on it.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.