SUMIFS with multiple OR criteria using CHILDREN?

JLC
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?

Tags:

Best Answer

Answers

  • L_123
    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
    JLC ✭✭✭✭✭✭
    edited 08/31/20

    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.

  • JLC
    JLC ✭✭✭✭✭✭

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

  • L_123
    L_123 ✭✭✭✭✭✭

    NP Glad we got it sorted :)