+ Reply to Thread
Results 1 to 4 of 4

SUMIF (OR SUMIFS) Using data from named ranges in dynamic drop down list

  1. #1
    Registered User
    Join Date
    12-19-2019
    Location
    Queens, NY
    MS-Off Ver
    2013
    Posts
    23

    SUMIF (OR SUMIFS) Using data from named ranges in dynamic drop down list

    Hello all,

    I have a formula that reads as follows:

    =SUMPRODUCT(SUMIF(Table1[Item No.],B6,Table1[Order Total])). The formula works fine as-is, but I've recently created a dynamic drop down list in my sheet, and I want to know if I can have the formula reference some of the named ranges that are chosen in the drop down list.

    for example, the formula above will calculate the order total of any given Item Number that's entered into cell B6 (all of the info is listed in an Excel-defined table named 'Table1'). My dynamic drop-down list spans two cells. The first cell (B5) contains all of the headers in Table1 (Item No., Type, Sub-Type, Destination Code). The second cell (B6), will list all of the data that is entered in Table1 that falls under the header chosen in cell B5. I'd like to be able to choose from the drop down list and have the formula reference the named range chosen (i.e. =SUMPRODUCT(SUMIF(Table1[Type],B6,Table1[Order Total])) or =SUMPRODUCT(SUMIF(Table1[Destination Code],B6,Table1(Order Total])).

    The only piece of data that is changing is the range in my formula. The criteria is always going to be listed in cell B6 even though it's chosen from the drop-down, and the sum range is always going to be "Order Total" in Table1.

    is there any way to accomplish this?

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,249

    Re: SUMIF (OR SUMIFS) Using data from named ranges in dynamic drop down list

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  3. #3
    Registered User
    Join Date
    12-19-2019
    Location
    Queens, NY
    MS-Off Ver
    2013
    Posts
    23

    Re: SUMIF (OR SUMIFS) Using data from named ranges in dynamic drop down list

    Hi Pepe,

    Thanks. I built a new, simplified sheet to try and illustrate what I'm looking to accomplish.

    The cells highlighted in yellow are what I currently have. I can run the formula to add the quantity and extension of any particular order, if I enter the 'order number' into the appropriate field ('1880' is entered in the example).

    What i want to do, is get use the drop-down list i've created (highlighted in red), where i can choose the headings and values and have the formula automatically update. I hope i've explained this correctly!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,649

    Re: SUMIF (OR SUMIFS) Using data from named ranges in dynamic drop down list

    These formulas reference the red cells C31:C32
    For Quantity: =SUMPRODUCT((Table1[#Headers]=C31)*(Table1=C32)*(Table1[Quantity]))
    For Order Total: =SUMPRODUCT((Table1[#Headers]=C31)*(Table1=C32)*(Table1[Extension]))
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Dynamic named ranges are not woking with SUMIFS function
    By Knightflier in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-31-2019, 10:56 AM
  2. 2 Named Ranges For 1 Drop Down List
    By bj90 in forum Excel General
    Replies: 6
    Last Post: 08-07-2019, 10:50 PM
  3. [SOLVED] Drop down list using combo box and dynamic named ranges
    By Pauly K in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2017, 06:11 AM
  4. [SOLVED] Data Validation & Dynamic named ranges - full list not showing
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2017, 06:55 AM
  5. Dynamic Named Ranges & Drop Down Lists
    By student6 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-02-2014, 01:07 AM
  6. [SOLVED] Dynamic Ranges as drop down lists based on another drop down list
    By Excel_Beginner_1 in forum Excel General
    Replies: 4
    Last Post: 05-15-2012, 03:31 PM
  7. [SOLVED] Drop-down-list with Named ranges
    By Chootje in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2006, 07:55 AM

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