# Excel SUM of SUMIF/SUMIFS with dynamic multiple criteria

I need to pass a multiple criteria list (a constant array) via cell reference rather than hard-typing it into my formula.
`=SUM(SUMIFS(sum_range,criteria_range,{"red","blue"}))`
But I would need to use this: `=SUM(SUMIFS(sum_range,criteria_range,\$A1))` where \$A1 is `{"red","blue"}`

I understand that one can use a range of cells to pass an array but I really need my condition to come from a single cell.
It seems that passing a constant array via cell reference only passes the first element to the formula (i.e. only "red" is used as a condition) and all the working examples I could find of this (here or here) are hard-typing the condition into the formula.

Any luck anybody ?

EDIT: I should add that my data set includes blank rows so it is not contiguous and in general, I'm looking for a not too convoluted solution that will work most of the time and with as little restrictions and caveats as possible.

Change the "Array" in A1 to a comma delineated list:

``````blue,purple
``````

No quotes or `{}`

Change the SUM to SUMPRODUCT and use this as the criteria:

``````TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+1,99))
``````

The `\$20` should be placed at the max number of choices possible. I just used it here as a placeholder, it can be more without problem but not less or it will skip any more than that.

Based on the formula you provided.

``````=SUMPRODUCT(SUMIFS(W\$12:W\$448,\$I\$12:\$I\$448,\$I474,\$J\$12:\$J\$448,\$J474,\$K\$12:\$K\$448,TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+1,99))))
``````

With cell A1 containing `{"red","blue"}` I then setup a named range `Condition` to which I assigned `=EVALUATE(\$A1)` and now I can pass my condition like so:
`=SUM(SUMIFS(W\$12:W\$448,\$I\$12:\$I\$448,\$I474,\$J\$12:\$J\$448,\$J474,\$K\$12:\$K\$448,Condition))`