Skip to main content

How to rank values by group in Excel?

In this article, I will talk about ranking values by group in Excel as below screenshot shown.
doc rank by group 1

Rank values by group

arrow blue right bubble Rank values by group

There is a formula to quickly rank values based on group.

Select a blank cell next to the data, C2 for instance, type this formula, =SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1 then drag autofill handle down to apply this formula to the cells you need.

In the formula, A2:A11 is the range containing the group values, A2 is the first cell in the list of group values, B2 is the first cell in the list which needed to rank, B2:B11 is the list that contains values needed to rank.
doc rank by group 2

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (12)
No ratings yet. Be the first to rate!
Arya
Arya
This comment was minimized by the moderator on the site
How to get unique ranks for Ex: Group Record RankA 100 1
If the same record repeat again I want rank 2 instead of 1.
Reply
0
0
Sarah
Sarah
This comment was minimized by the moderator on the site
Hi Sunny, I will be so grateful for suggestions. I conducted a survey of stakeholders for our nonprofit. Our dataset of 53 responses now includes (1)type of stakeholder WHICH CAN INCLUDE MORE THAN ONE TYPE (ex: volunteer and donor); (2) rank order of top 3 skills (from a list) you feel are needed for your job; (3) rank order top 3 skills young people need today (from a list that is similar to but not exactly the same as the one used in the prior question). The format was to select from drop down list.Is it possible to analyze to see if Donors (for example) as a group prioritize similarly?
Thanks very much!
Reply
0
0
Nick
Nick
This comment was minimized by the moderator on the site
Thanks for this solution! Is there a alteration that would allow me to omit blanks from the ranking?
Reply
0
0
Sunny
Sunny     Nick
This comment was minimized by the moderator on the site
Hi, Nick, you can use the Filter feature in Excel to filter out blank cells firstly, and hide them or remove them, then use above formula to rank.
Reply
Report
0
0
Ajwad
Ajwad
This comment was minimized by the moderator on the site
hi sunny, i have a complex query here but need to visualize it to better understanding. how can i contact u with visualization?v
Reply
0
0
Brian Q
Brian Q
This comment was minimized by the moderator on the site
How can I get it to rank in reverse order, from smallest to largest?
Reply
0
0
Sunny
Sunny     Brian Q
This comment was minimized by the moderator on the site
Hi, Brian Q, you can use this formula =SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1 to ranke from smallest to largest
Reply
Report
0
0
Kip
Kip     Sunny
This comment was minimized by the moderator on the site
I'm sorry, but I don't see a difference between this formula and the one above.
Reply
0
0
JB
JB     Kip
This comment was minimized by the moderator on the site
B2> ; just reverse from less than to greater than.
Reply
0
0
The Inquirer
The Inquirer
This comment was minimized by the moderator on the site
This is EXCEL-lent (see what I did there?)! Now that you have the rankings by group, how do you find out % from #1 rank within the group? For example, Group A has record 100 as #1, and record 93 as #3 ranked. If I wanted to add another column to find out how far off in percentage record #3 (93) is from record #1 (100), what is the formula to achieve this? The result should be rank #1 is 0%, and rank #3 is 7% off ((100/93)-1) from rank #1
Reply
0
0
Greg Tappenden
Greg Tappenden     The Inquirer
This comment was minimized by the moderator on the site
Devide the subject by the max of the group using a max if.

Like this:
=C2/MAXIFS(C:C,B:B,B2)
Reply
0
0
Sunny
Sunny     The Inquirer
This comment was minimized by the moderator on the site
Sorry I have not found a formula or other solution can handle ur question.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations