+ Reply to Thread
Results 1 to 4 of 4

Dynamic Named Range with Unique Values Only for Sumproduct Function

  1. #1
    Registered User
    Join Date
    02-06-2014
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007
    Posts
    12

    Dynamic Named Range with Unique Values Only for Sumproduct Function

    I am working on a report for a peer and had mostly completed it when I discovered an issue with one of my formulas.

    I am using a dynamic named range to select a list of member numbers so that I can sum the results for all members in the list. The problem is that a member number may appear multiple times or multiple member numbers may appear in the list, so when used in a sumproduct function, it is multiplying results by the number of times each number appears in the list. Also, the member list has a variable amount of members list. One list may include three numbers, another may have 20, which is why I used a dynamic range.

    I'm trying to find a way to make the dynamic named range only include each value in the Member List once. I have attached a sample file below.

    Thank you for any help you can provide.

    ExcelForum Sample.xlsx
    Last edited by tlscowden; 07-16-2015 at 11:40 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Dynamic Named Range with Unique Values Only for Sumproduct Function

    Don't use SUMPRODUCT, use a Pivot Table.



    A
    B
    C
    D
    E
    F
    1
    Member
    Sales
    2
    123456
    9874
    3
    123456
    456
    4
    456789
    1234
    5
    6
    Row Labels
    Sum of Sales
    7
    123456
    10330
    8
    456789
    1234
    9
    Grand Total
    11564
    10



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-06-2014
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Dynamic Named Range with Unique Values Only for Sumproduct Function

    The problem I run into with a pivot table is that I'm not creating the report for my own use. The person that requested the report has asked for it to be "idiot proof" to the point that they just have to add a new worksheet each week/month when the source data is updated. There will be 38+ months of data and I don't know that the skill set of the person updating the report includes creating a pivot table each month.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Dynamic Named Range with Unique Values Only for Sumproduct Function

    I don't really think I can help you. I've met some very imaginative and innovative idiots in my time. No matter how simple you try to make it, they'll still screw it up. I don't really understand the two lists, both with multiple entries for member numbers. Ideally, you need a "master list" of member numbers. You could then use that for SUMIF or SUMIFS formulae to get your results.

    If, as is implied, you plan to have 38+ worksheets to reflect 38+ months of data, I suspect that you will find it very difficult to analyse the data effectively.

    In my opinion, and that's all it is, you would be far better have a single data sheet where all the data is copied and pasted. Make that data into a Structured Table and you can easily sort it and filter it, and it will be very straightforward to create and refresh one or more Pivot Tables to analyse the data.

    Regards, TMS

+ 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. Unique dynamic named range
    By pauldaddyadams in forum Excel General
    Replies: 1
    Last Post: 02-06-2015, 03:31 PM
  2. Finding Unique Values in Named Range
    By jonboy6257 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-02-2014, 04:58 PM
  3. [SOLVED] SumProduct of Unique values in a dynamic length column
    By ddander54 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-20-2013, 05:24 PM
  4. [SOLVED] Dynamic Named Range Help - Range Based on Values in Column
    By Filibuster in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-03-2012, 11:13 AM
  5. Way to retrieve values from a dynamic named range?
    By OLDWEASEL in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-08-2011, 09:53 AM
  6. List Unique Values in a Named Range
    By tomlancaster in forum Excel General
    Replies: 4
    Last Post: 02-17-2011, 08:53 AM
  7. dynamic named range function
    By MJB in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-06-2005, 07: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