Excel - SUMIFS over a set

Question

I am trying to use a sumif where the criteria is that column B equals 1 or 11:

=SUMIFS(A:A,B:B,{1,11})

The formula above works perfectly. But now what I would like to do is store {1,11} in a cell (e.g. cell C1). Then:

=SUMIFS(A:A,B:B,C1)

This no longer works, and it seems to pull up C1 as "{1,11}". Even though there are no visible quotes in cell C1, when I highlight C1 and press F9 in the sumif formula, quotes do come up.

Is there any way to do this? I want to be able to set the array {1,11} as a variable in a separate cell, because this could change. I do not want it hardcoded within the sumifs formula.

Thanks!!

Answer 1

One possibility is you can use text in C1:

'{1,11}

And then define a name to convert it to an array:

EvalC1 refers to

=EVALUATE($C$1)

Note you can use the name in your array formula:

=SUMIFS(A:A,B:B,EvalC1)

Answer 2

Place your desired lookup in C1 and down, this is dynamic

=SUMPRODUCT(SUMIFS(A:A,B:B,C1:INDEX(C:C,MATCH(1E+99,C:C))))

Just make sure there are no other numbers in column C and the list is contiguous.