SUMIFS with multiple OR criteria using CHILDREN?

JLC

Just curious. Looking for a way to speed up this scenario:

=SUMIFS([Range to sum]:[Range to sum], [Criteria range]:[Criteria range], OR(@cell = [Criteria]1, @cell = [Criteria]2, @cell = [Criteria]3))

Is there a way to transpose that OR statement into something using CHILDREN so that I can plop a list of a large number of variables to SUM? Or am I stuck using OR?

L_123

Try this:

=sumifs([Range to sum]:[Range to sum], [Criteria range]:[Criteria range],contains(@cell,join(children(h$1),"*"

Where your criteria is in column H and the parent is row 1.

L_123

You could try contains(@cell,join(h$1:h$10,"*")) as your criteria where h1:h10 is your range of references. (* is a unique value to try to segment your data. If you have values with * in them, try using a special character there instead that you wouldn't use. Examples being ~ @ # ^ ! and any other unique character)

JLC

Thanks @L@123, that works in a pinch too. I'm trying to avoid directly referencing specific cells/a range of cells. However, just realized I can also use a helper column, a blanket formula @row'ing each specific variable's SUMIF, then SUM the CHILDREN of that section.

Would still be interested in a "true" CHILDREN way of doing this in one formula if that's at all possible and anyone wants to take a stab! Otherwise happy to list @L@123's answer as the best answer.

L_123

Try this:

=sumifs([Range to sum]:[Range to sum], [Criteria range]:[Criteria range],contains(@cell,join(children(h$1),"*"

Where your criteria is in column H and the parent is row 1.

JLC

@L@123 great workaround. Thanks so much for the insight, I hadn't used JOIN before in that context. Appreciate that.

L_123

NP Glad we got it sorted :)