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!

Hi,

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

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