+ Reply to Thread
Results 1 to 3 of 3

Sumproduct ranks without duplicate

  1. #1
    Registered User
    Join Date
    10-19-2021
    Location
    london
    MS-Off Ver
    2021
    Posts
    8

    Sumproduct ranks without duplicate

    Hi,

    Its me again. I am trying to get a sumproduct ranking without any duplicates.

    =IF(J24="","",SUMPRODUCT((I24=$I$6:$I$1500)*(B24<$B$6:$B$1500))+1)

    At the moment I am only getting Dupplicate rank should they match. How can I go about getting this changed to the next rank number. If I am able to get it in sequenctial order then I am able to create a table that shows me my top sold menu items for each catagory.

    Assuming R19=12, I am not able to get the item for this since i have two products that are ranked 11 hencefourth skipping 12. I found some solutions but this is just pure countif.

    =INDEX($A$6:$K$600, SMALL(IF(COUNTIF($S$7, $I$6:$I$600)*COUNTIF(R19, $K$6:$K$600), MATCH(ROW($A$6:$H$600),ROW($A$6:$H$600))), ROWS($A$1:A1)), COLUMNS($A$1:$A$1))

    Regards,

    Mustafa
    Attached Files Attached Files

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Sumproduct ranks without duplicate

    Try this in K6:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Registered User
    Join Date
    10-19-2021
    Location
    london
    MS-Off Ver
    2021
    Posts
    8

    Re: Sumproduct ranks without duplicate

    Quote Originally Posted by WideBoyDixon View Post
    Try this in K6:

    Please Login or Register  to view this content.
    WBD
    many thanks. its working perfectly. im now able to finalise my report.

+ 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. [SOLVED] Partial duplicate - Sumproduct VBA Help!
    By Kanage in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-23-2018, 11:09 AM
  2. [SOLVED] Rank function without duplicate ranks
    By rdewar in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-04-2018, 02:16 PM
  3. Two stage sorting function which eliminates gaps and sorts duplicate ranks?
    By wdjohnson in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-01-2016, 06:26 PM
  4. SumProduct on Duplicate values
    By raja_puligadda in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-30-2015, 04:29 AM
  5. [SOLVED] Using SumProduct and Count To Calculate % Ranks
    By SteveC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-28-2006, 09:15 AM
  6. [SOLVED] How do I differentiate between duplicate ranks?
    By Pez in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2006, 07:15 PM
  7. [SOLVED] DUPLICATE RANKS
    By Matthew in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-22-2005, 09:10 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