+ Reply to Thread
Results 1 to 8 of 8

Adding Dynamic Criteria to SUMIFS from Drop-Down Box

  1. #1
    Registered User
    Join Date
    01-02-2020
    Location
    NJ
    MS-Off Ver
    2016
    Posts
    5

    Adding Dynamic Criteria to SUMIFS from Drop-Down Box

    Hello, I would greatly appreciate it if i can get help on an issue i spent a lot of time on.
    I am trying to update a SUMIFS formula. I have a database that is not a table range. It is a hyperion retrieve that refreshes. In this database i have several names, for example "FY_Budget", etc...

    In the sumifs formula, when i enter the name "FY Budget" in the sum range, the formula works perfectly fine.
    The problem is I want to replace the name in the sum range and link it to a cell (drop down list with names i created). It is not working.
    Can someone please help? My other criteria's don't need to be dynamic, but the sum range should be. Thank You!
    Last edited by AliGW; 01-02-2020 at 12:38 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: SUMIFS Formula: SUM RANGE issue please help!

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new here, I will do it for you this time.)

    There are instructions at the top of the page telling you how to attach your workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Agawam, MA
    MS-Off Ver
    Microsoft Office 365
    Posts
    842

    Re: Adding Dynamic Criteria to SUMIFS from Drop-Down Box

    maybe utilize an Indirect() function inside your sumif.
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  4. #4
    Registered User
    Join Date
    01-02-2020
    Location
    NJ
    MS-Off Ver
    2016
    Posts
    5

    Re: Adding Dynamic Criteria to SUMIFS from Drop-Down Box

    Thank you, it looks like the indirect function is working.

    I was really hoping though to be able to link the sum range to a cell that has a data validation list with names (Names for columns in database).

    Would greatly appreciate it if someone can help with this.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Adding Dynamic Criteria to SUMIFS from Drop-Down Box

    See the instructions at the top of the page telling you how to attach a sample workbook.

  6. #6
    Registered User
    Join Date
    01-02-2020
    Location
    NJ
    MS-Off Ver
    2016
    Posts
    5

    Re: Adding Dynamic Criteria to SUMIFS from Drop-Down Box

    Hello, I have attached a sample workbook with more details. Hopefully this makes what i'm trying to accomplish clear.

    Basically instead of selecting the name FY_AOP in the sumifs formula, i want to link it to cell B5 in the content sheet.

    Thank you!
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Agawam, MA
    MS-Off Ver
    Microsoft Office 365
    Posts
    842

    Re: Adding Dynamic Criteria to SUMIFS from Drop-Down Box

    =SUMIFS(INDIRECT(Contents!B5),BU,$E$1,Account,$C$10,Country,$E$2)

    it looks like your other named ranges FY_2018 and 2019 are not actual named ranges on the worksheet you gave us. Regardless though this should work for you, so when you change B5 using the drop down box it will automatically update the sumif

  8. #8
    Registered User
    Join Date
    01-02-2020
    Location
    NJ
    MS-Off Ver
    2016
    Posts
    5

    Re: Adding Dynamic Criteria to SUMIFS from Drop-Down Box

    Thank You. Yes that works.

    I was hoping to find a way to link to cell Contents!B5 without using indirect.
    Just trying to make the sumifs formula look as simple as possible.

    Any other ideas? My guess is thats there's a format error but can't figure it out.
    Thanks again for your help!

+ 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] SUMIFS Formula Issue
    By SamDz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2019, 08:26 AM
  2. [SOLVED] SUMIFS formula to replace SUMPRODUCT because of ressource issue
    By lillumultipass in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 12-14-2017, 08:05 AM
  3. Issue With Not Equal To in SUMIFS Formula
    By Comptka in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-06-2015, 02:29 PM
  4. sumifs + offset formula issue
    By jw01 in forum Excel General
    Replies: 6
    Last Post: 02-11-2015, 11:05 AM
  5. I want Sumifs formula with Or criteria in same Range
    By lulupatel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-19-2014, 12:03 PM
  6. SUMIFS Formula Issue
    By keithd203 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-18-2013, 04:15 PM
  7. [SOLVED] Sumifs Date Range Formula
    By robbiedew101 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2013, 03:51 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