Multilevel Collect/Sumifs Formula Difficulty

L_123
L_123 Community Champion
edited 12/09/19 in Formulas and Functions

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)

 

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!