+ Reply to Thread
Results 1 to 11 of 11

Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring #N/A

  1. #1
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

    Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring #N/A

    Hello Excel Experts!

    I'm trying to create a dashboard here that could automatically spit out the top 5 customers for each month, ignoring #N/A.

    Since it's per month basis, therefore, top 5 customers could change every month, thus it's dynamic.

    In the attached, I've given a sample of a full data, and a pivot table (NOTE: Pivot and Data does values not match)

    As you can see in the "Desired Result" sheet, that's how my dashboard will look like per month. These results were taken through pivot table (manual sorting each month largest to smallest and copy and paste). IF POSSIBLE, I would not like to have a pivot table (to save time creating a macro to refresh the pivot table and sorting each month). But if it's un-doable, or it'll take the macro to run longer, I'm OK in getting the final results through pivot table.

    If my explanation is not cleared, I hope that the attached file would clarify further!

    Your input and help is greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    Hi there. I don't think that you can get exactly what you want. If, for example, customer Y (who doesn't appear on your top 5 anywhere suddenly sells 100,000 Units in March, that'll not work well with your fixed row order. So, with the aid of one helper column per month (to get rid of the pesky #N/A error, I came up with this...
    Attached Files Attached Files
    Glenn



  3. #3
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    Hi

    Glenn Kennedy done a brilliant Job?

    Just little change from Row

    Cell B4 Instead use
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    You see where I highlight Blue? this will course you problem if you put in any Row?

    Change to this on Cell B4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy down and cross.

    See highlight Blue Row 1:1? You can put in any row then copy down to top 5!!

    See the file highlight yellow!

    Regard
    micope21
    Attached Files Attached Files
    Last edited by micope21; 06-24-2014 at 05:54 AM.
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,824

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    Pl see attached with ARRAY formula and without helper columns.
    Attached Files Attached Files

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,952

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    find the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    Hi, Siva. i do not think that your approach is fully dynamic and therefore may not meet the OP's requirements. Try changing Pivot E13 to 100,000. Customer K does not go to the top of the list. try changing cell E27 to 100,000. Customer Y does not appear on the list...

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,952

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    Hi, Mr Glenn as per dluhut example the Custer Order is Based on January month data, so that it was prepared

  8. #8
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    First of all, THANK YOU so much for all of your contribution!

    nflsales, is so far the closest to what I want, with the use of a helper column! That being said, the reason why I wanted it to be this way is for "tracking" purposes as well. So that if in a given month, the previous top customer suddenly changed, or has a new top customer, then we'll be able to know.

    That being said, since I would incorporate this with a macro, wondering if there's anyway that I could get away without a helper column? If not, I'm happy with the result that nflsales has given!

    nflsales, care to explain a little of your formula on the helper columns? I'm assuming that the helper column is taking only the top 5 customer, ignoring the #N/A in each given month?

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,952

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    Yes, you are right mr dluhut

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    Quote Originally Posted by nflsales View Post
    Hi, Mr Glenn as per dluhut example the Custer Order is Based on January month data, so that it was prepared
    My mistake. You're quite correct...

  11. #11
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

    Re: Dynamic Ranking (Top 5 Customer) - Sumif Array / Sumproduct - with Criteria, Ignoring

    I'll mark this thread as solved for now! Reps up to you nflsales, and of course everyone who helped!

    Thank you once again!

+ 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. Sumproduct with multiple criteria and ignoring text values
    By soapy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2014, 10:50 AM
  2. SUMPRODUCT or SUMIF using an array for criteria
    By rarascon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2013, 03:14 PM
  3. [SOLVED] Help with sumproduct for mutliple criteria ranking with a tie breaker
    By sshahils in forum Excel General
    Replies: 2
    Last Post: 07-17-2012, 11:39 AM
  4. Dynamic Ranking Formula w/ Multiple Criteria
    By nwd9s in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-30-2011, 07:35 AM
  5. Sumproduct with dynamic array
    By benaw in forum Excel General
    Replies: 3
    Last Post: 10-18-2009, 07:24 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