Search code examples
arraysexcelsumifsmultiple-conditions

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:
=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.

Solution

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