Multilevel Collect/Sumifs Formula Difficulty

L_123

I am attempting to parse a large amount of sheets together and have hit a snag in a formula.

My data looks like below:

 

H | A | B | C

1 | 7  | 4 |  9

0 | 2  | 1 |  5

1 | 5  | 3 |  1 

H is a helper column displaying a 1 if the data should be utilized and a 0 if not. 

I need to reference a range as a whole in order to keep my number of references below the maximum allowed /sheet or this would be rather straightforward.

I need to have a formula that only utilizes 2 references, H:H and A:C, but sums a single column if the criteria in H is met.

Without the multicolumn reference the formula would be simple, 

sumif(A:A,1,H:H) or something similar

I haven't been able to get this to work with the multicolumn reference though.

 

Any help or ideas would be appreciated. If I don't get this to work it is 4 extra sheets, a large amount of extra work, and an extra layer of complexity for future editing that I really don't want. (I don't want to use the pivot table feature as it is to slow to update, maybe in the future it can be used)

 

Paul Newcome

Have you tried summing across the rows with an additional helper column in the data sheet then using the original helper column as the criteria to sum the new helper column? That would really cut down on cross sheet referencing as you'd only be looking at one column instead of 3.

L_123

That was a great suggestion, and it led me to a partial solution. I have added a helper row across the top of one of the sheets, and simply index the values out of them. My only issue is that now I have to refresh my results page when I change the criteria, but I can get over that.

 

Thanks!