+ Reply to Thread
Results 1 to 4 of 4

Dynamic sumif based on changing list of inclusion criteria?

  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    8

    Dynamic sumif based on changing list of inclusion criteria?

    Hi --

    This is a semi-complicated request that I'm 98% sure is feasible in excel, I just can't figure out how.

    I have one tab of data that has variable names up top, with weekly data below:

    A B C D E F G
    10/11/2014 1 0 0 0 0 0 0
    10/18/2014 2 0 0 0 0 0 0
    10/25/2014 3 2 0 0 0 5 10
    11/1/2014 4 3 2 0 0 5 12
    11/8/2014 5 4 3 0 0 5 7
    11/15/2014 6 5 4 2 0 5 10
    11/22/2014 7 6 5 3 0 5 30
    11/29/2014 0 7 6 4 0 5 0
    12/6/2014 0 0 7 5 0 5 0
    12/13/2014 0 0 0 6 0 0 0
    12/20/2014 0 0 0 7 2 0 0
    12/27/2014 0 0 0 0 3 0 0
    1/3/2015 0 0 0 0 4 0 0
    1/10/2015 0 0 0 0 5 0 0


    Then, I have another tab that has some custom variables that are combinations of the above variables A - G.

    1 2 3 4
    A A C A
    B G D B
    C E C
    D

    Custom variables 1 - 4 have varying combinations of the original variables A - G. The issue is, a variable A - G can exist in *multiple* custom roll up variables. What I am trying to do, on the first tab, is have additional columns for custom variables 1 - 4, that are Sums of all included variables in that roll up at a weekly level. So, for custom variable 1, the value for 11/15/2014 is 15 (6 + 5 + 4, from A + B + C). I need weekly sums for each of my custom variables, based on what original variables roll up into those.

    The kicker is that my roll up list on the second tab can change -- I need to setup the formula so I can add or remove variables A - G under my custom variables 1 - 4, and the sumifs on the first tab will dynamically update. It seems like this is possible with some kind of index/sumif/vlookup, I'm just not sure how.

    Any insight appreciated!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Dynamic sumif based on changing list of inclusion criteria?

    Not sure I could help you, but

    You get better help on your question if you add a small excel file, without confidential information.

    Please also add manualy the expected result in your file.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    09-04-2017
    Location
    World
    MS-Off Ver
    2007
    Posts
    6

    Re: Dynamic sumif based on changing list of inclusion criteria?

    Hi,

    shouldn't the custom variable 1 evaluation for 11/15/2014 be 17 (6 + 5 + 4 + 2, from A + B + C + D)?

  4. #4
    Registered User
    Join Date
    10-24-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic sumif based on changing list of inclusion criteria?

    YES I messed up my own post (didn't see the D there as I was summing up for the example)

+ 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] Dynamic Validation List based on Criteria
    By Steve Bowden-Jones in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 10-01-2014, 03:17 AM
  2. Dynamic drop down list, based on a criteria
    By marcinpec in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-17-2014, 11:00 AM
  3. [SOLVED] Create list of combinations based on changing criteria
    By NamiSama in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-15-2014, 01:41 PM
  4. [SOLVED] Create Dynamic Data Validation List based upon Criteria
    By stubbsj in forum Excel General
    Replies: 6
    Last Post: 06-04-2012, 08:33 AM
  5. create a dynamic drop down list based on specific criteria
    By stevegrobertson in forum Excel General
    Replies: 4
    Last Post: 02-21-2012, 08:07 PM
  6. Dynamic sumif ranges based on Hlookup criteria
    By Dial1 in forum Excel General
    Replies: 2
    Last Post: 10-24-2011, 01:21 AM
  7. Replies: 3
    Last Post: 08-21-2011, 08:22 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