How to Rank duplicate items with same rank based on its corresponding sum total

[BIJUJB]

Hello,

1)How can I rank duplicated items with unique rank based on its sum value of another cell in a single formula string.
How can I rank all rows under Desired Rank of Patrick as 1 as his total value is 55 and Amy as 2 as her total value is 50 and John as 3 as his total value is 30 ..by using a single formula in excel.

I do not need Patrick value as 1 and Amy value as 4 as in tie ranking but only sequentially.

Also Pls let me know how to ascend and descent the rank order if possible.
Name Value Desired Rank
Amy 20 2
Amy 15 2
Amy 15 2
John 10 3
John 20 3
Patrick 30 1
Patrick 25 1

2)Also if there are more than one criteria used , How do I rank it sequentially based on the volume sold? for eg. If Amy sold most number of Oranges rank her as 1 and rank next person who sold second highest product and so on based on value sold. Any help is greatly appreciated...
Name Product Sales Desired Rank
Amy Apples 20 5
Amy Oranges 50 1
Amy Oranges 30 1
John Apples 10 2
John Apples 20 2
Patrick Grapes 30 4
Patrick Apples 25 3


Link to a similar question raised but cannot fully resolve my problem..
http://www.excelforum.com/excel-form...ml#post4051354

[daddylonglegs]

For the first question, assuming you have names in A2:A8 and values in B2:B8 you can use this array formula in C2

=SUM(IF(SUMIFS(B$2:B$8,A$2:A$8,A$2:A$8)>SUMIFS(B$2:B$8,A$2:A$8,A2),IF(MATCH(A$2:A$8,A$2:A$8,0)=ROW(A$2:A$8)-ROW(A$2)+1,1)))+1

confirmed with CTRL+SHIFT+ENTER and copied down to C8

If you want to reverse the rankings just use < in place of >

You can still get duplicate ranks if the totals are the same for different names

For your second formula, are you sure those ranks are correct? I'd expect it to be like this?

Name Product Sales Desired Rank
Amy Apples 20 5
Amy Oranges 50 1
Amy Oranges 30 1
John Apples 10 2
John Apples 20 2
Patrick Grapes 30 2
Patrick Apples 25 4

Patrick/Grapes = 30 and John/Apples is 30 so those 2 would be tied 2nd while Patrick/Apples is then 4th with 25

If that isn't correct can you explain your logic for the ranks shown?

[BIJUJB]

Hello Daddylonglegs,

Thanks so much for the above formula where you have combined both formulas to one ( Sum ifs and rank by volume). Works perfectly for me. Only problem I see is when I have to run this formula over 2000 rows it takes 30 seconds or more to complete the process . I see 4( processers running 2% complete) message at the bottom of the excel.

Second formula works fine. I think your logic was correct, My calculation was wrong for the ranking..

So to share with you What I was doing is I get lots of "X" values for every country (multiple rows of same country also) and have to summarize in another sheet based on the which country has the largest volume of "X" value. So my summary sheet update automatically by Vlook up 1 with Rank 1 country by volume and so on based on highest to the lowest volume by the ranking formula you just shared. I have learned to create a summary file of ("Top 15") which update automatically based on the raw data provided

Thank you so much.