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.
So, instead of this:
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.

2 answers

  • answered 2018-03-13 20:34 Scott Craner

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


    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.

    enter image description here

    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))))

  • answered 2018-03-13 20:34 YeO

    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: