+ Reply to Thread
Results 1 to 7 of 7

1st most common products sold, 2nd, ....10th

  1. #1
    Registered User
    Join Date
    06-21-2012
    Location
    SF, California
    MS-Off Ver
    Excel 2003
    Posts
    5

    1st most common products sold, 2nd, ....10th

    Hi,
    For Excel 2007:
    I need a formula (maybe 2 if needed) to list top 10 products sold.
    I have over 100 different products listed in Col A, copies sold in Col B.

    I would like 2 new columns output (Col C, Col D):
    Under Col C, 10 values:
    No. 1 seller is: (This is the most common product sold)
    No. 2 seller is: (This is the 2nd most common product sold)
    No. 3 seller is:
    ..
    ..
    Under Col D, 10 values:
    Copies sold from No. 1 seller:
    Copies sold from No. 2 seller:
    ..
    ..

    Thank you

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: 1st most common products sold, 2nd, ....10th

    Why not just sort columns A and B by quantity sold?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    06-19-2012
    Location
    N/A
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: 1st most common products sold, 2nd, ....10th

    Enter the following in cell C2 as an array formula (CTRL-SHIFT-ENTER) and drag it down for as many top sellers as you want:

    Please Login or Register  to view this content.
    In cell D2, enter the following with CTRL-SHIFT-ENTER and drag down:

    Please Login or Register  to view this content.
    The formula is currently looking at the range A2 through B100, so you'll need to adjust those values as necessary. Hope this helps.
    If I have helped, please click the star below.

  4. #4
    Registered User
    Join Date
    06-21-2012
    Location
    SF, California
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: 1st most common products sold, 2nd, ....10th

    Rob, thanks for the reply, but both of your formulas are the same exact thing..
    I need the formula in C2, which will show:

    No. 1 seller is:
    No. 2 seller is: ....etc.


    I am attaching a sample excel of what I need.

    I don't want to use the Filter/sort in Excel. I just need formulas.

    Thanks so much in advance..
    J.S.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: 1st most common products sold, 2nd, ....10th

    With a pivot table and then sort on totaal (= total)
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: 1st most common products sold, 2nd, ....10th

    Or with formula.

    I prefer the pivot table.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-19-2012
    Location
    N/A
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: 1st most common products sold, 2nd, ....10th

    Quote Originally Posted by jameel30 View Post
    Rob, thanks for the reply, but both of your formulas are the same exact thing.
    Probably because I accidentally pasted the same formula twice. Oops.

    Try this in C2:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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