SUMIFS with multiple OR criteria using CHILDREN?


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?

Popular Tags:

Best Answer


  • L@123[email protected] ✭✭✭✭✭

    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)

  • Jaye CascianoJaye Casciano ✭✭✭✭✭
    edited 08/31/20

    Thanks @[email protected], 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 @[email protected]'s answer as the best answer.

  • Jaye CascianoJaye Casciano ✭✭✭✭✭

    @[email protected] great workaround. Thanks so much for the insight, I hadn't used JOIN before in that context. Appreciate that.

  • L@123[email protected] ✭✭✭✭✭

    NP Glad we got it sorted :)

Sign In or Register to comment.