+ Reply to Thread
Results 1 to 4 of 4

Rank duplicate values with COUNTIFS

  1. #1
    Registered User
    Join Date
    07-30-2020
    Location
    Utah, United States
    MS-Off Ver
    2016
    Posts
    2

    Rank duplicate values with COUNTIFS

    Hi experts,

    I have a data set looking at 3 types of fruit, their order dates, and order cost. I want to rank the line items by earliest date. However, since some orders have the same order date, I get duplicate ranks (see two rows of Apples ranked as #4 in the attached image).

    I am using a COUNTIFS function to rank the fruit. How can I adjust the formula to avoid duplicate rank values? Should I use something different from COUNTIFS? Is there a way to incorporate the order cost as a second differentiator to help solve this problem?
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Rank duplicate values with COUNTIFS

    Quote Originally Posted by thisistyty View Post
    I want to rank the line items by earliest date. However, since some orders have the same order date, I get duplicate ranks (see two rows of Apples ranked as #4 in the attached image).
    Intuitively this makes sense to me as the correct result. Like, orders coming in on the same date are tied, so they should share a rank.

    Is there a way to incorporate the order cost as a second differentiator to help solve this problem?
    Yeah this or something like it is doable... but is obfuscating the ranking criterion with a new-and-unrelated second criteria actually what you want?
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Registered User
    Join Date
    07-30-2020
    Location
    Utah, United States
    MS-Off Ver
    2016
    Posts
    2

    Re: Rank duplicate values with COUNTIFS

    Thanks for your response. I actually just figured out the answer.

    =COUNTIFS($C:$C,C4,$D:$D,"<"&D4)+COUNTIFS(C$4:C4,C4,D$4:D4,D4)

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Rank duplicate values with COUNTIFS

    Please take the time to review our rules. There aren't many, and they are all important.

    It appears that the answer you figured out was posted on the other site where you asked your question.

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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. Rank with Duplicate values
    By avij90 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2019, 11:15 AM
  2. [SOLVED] Rank lowest to highest with duplicate values
    By Stuepef in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-05-2018, 07:55 PM
  3. Eliminating duplicate values when using RANK
    By sosulli80 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2016, 06:16 AM
  4. Using Rank Formula on List with Duplicate Values FILE ATTACHED
    By alexx579 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-21-2016, 02:39 PM
  5. [SOLVED] Hide Duplicate Rank Values
    By trandle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-06-2015, 11:36 AM
  6. [SOLVED] Return Column Headers based on row rank with duplicate values in row
    By carlwin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-31-2013, 12:24 AM
  7. Rank Duplicate Values Sequentially
    By kalyanverma in forum Excel General
    Replies: 3
    Last Post: 12-17-2009, 02:32 PM

Tags for this Thread

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