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?

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.