+ Reply to Thread
Results 1 to 8 of 8

Automate Ranking on Multiple Criteria

  1. #1
    Registered User
    Join Date
    11-15-2011
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2016
    Posts
    15

    Automate Ranking on Multiple Criteria

    Hello all, I trust you are all well...

    I have a situation where I need to rank cells based on a value... I can do this manually but I'm looking for a formula to make it basically automated...

    I have attached a file for you.

    Column T are the values I need to rank

    Column U is my manual formula

    Each different coloured area before Column U is the data I need to use to then Rank Column T value

    Is there a way to rank the values in Column T only when Columns A, B, C, D, E are all the same?

    I hope this all makes sense, if not please let me know.

    Cheers
    Dan
    Attached Files Attached Files
    Last edited by AliGW; 06-27-2021 at 11:13 AM.

  2. #2
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Rank formula

    Cell U2 formula , Drag down

    HTML Code: 

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Rank formula

    This is enough:

    =SUMPRODUCT(N(($A$2:$A$211=A2)*($C$2:$C$211=C2)*($E$2:$E$211=E2)*($T$2:$T$211<T2)))+1

    Matching on B and D is unnecessary as they are already dependent on A and C respectively.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Automate Ranking on Multiple Criteria

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you may have forgotten this, I've done it for you today.)

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Automate Ranking on Multiple Criteria

    With Power Query

    Please Login or Register  to view this content.
    See attached file:

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Registered User
    Join Date
    11-15-2011
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Automate Ranking on Multiple Criteria

    I love this site so much!

    Thank you both AliGW (sorry about the original post title) and wk9128...

    Very much appreciated
    Cheers

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Automate Ranking on Multiple Criteria

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Automate Ranking on Multiple Criteria

    Glad to help and thanks for the rep.

    Don't forget to add the solved tag to the thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 02-08-2016, 06:55 PM
  2. [SOLVED] forcing a rank on 1 through 5 (no dups) using the rank formula in Excel
    By denver1717 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-22-2015, 08:28 AM
  3. Replies: 6
    Last Post: 11-30-2013, 09:14 AM
  4. Replies: 1
    Last Post: 11-30-2013, 06:48 AM
  5. [SOLVED] RANK formula cannot rank duplicates
    By unpluggedmusic in forum Excel General
    Replies: 5
    Last Post: 10-13-2012, 12:59 PM
  6. Formula to Rank and populate values base don Rank
    By Excel Dumbo in forum Excel General
    Replies: 3
    Last Post: 10-09-2012, 02:02 AM
  7. Replies: 1
    Last Post: 08-15-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1