How can I use Descendants/Ancestors in a cross sheet SUMIFS to filter result

I have three tiers of data on my data sheet, Type, Region, and Month, with two columns, primary and Qty. I want to pull data from that sheet to a metrics sheet. The Metrics sheet is meant to sum Qty for a given Type and Month, regardless of Region. I can pull the cross sheet data based on Month, but I'm unsure how to use the Type criteria. This is what I'm trying currently: SUMIFS({datasheet Qty}, {datasheet primary}, @cell = primary@row, {datasheet primary} Descendants(@cell) = "Type1").

I've attached screenshots of Data and Metric sheets.

Thank you, John

Find more posts tagged with

Accepted answers

Megan Ryzenga

For sheets with parent/child rows, I like to always include a hidden "Hierarchy helper" column so I can use that as a condition in any of my formulas/functions. It would look like this:

=COUNT(ANCESTORS())

What this will result:

Parent row (Type1) = 0

Child row (Region1) = 1

Grandchild row (1, 2, 3) = 2

(etc.)

and you can adjust your formula to reference the hierarchy helper column that = "2" to reference yellow, green and blue rows you would like to sum.

John Knipper

I just found this which answers my question about using ANCESTORS with a cross sheet reference . I'm assuming the information should be updated to include DESCENDANTS. Would someone confirm?

"The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS. Using a reference from another sheet with these functions will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula. "

All comments

Megan Ryzenga

For sheets with parent/child rows, I like to always include a hidden "Hierarchy helper" column so I can use that as a condition in any of my formulas/functions. It would look like this:

=COUNT(ANCESTORS())

What this will result:

Parent row (Type1) = 0

Child row (Region1) = 1

Grandchild row (1, 2, 3) = 2

(etc.)

and you can adjust your formula to reference the hierarchy helper column that = "2" to reference yellow, green and blue rows you would like to sum.

John Knipper

I thought about using a Helper column (your suggestion will work), but I was hoping there's a way to do it without a Helper column.

My last attempt is like this: SUMIFS({datasheet Qty}, {datasheet primary}, @cell = primary@row, {datasheet primary} Index(Ancestors(@cell) = "Type1").

John Knipper

I just found this which answers my question about using ANCESTORS with a cross sheet reference . I'm assuming the information should be updated to include DESCENDANTS. Would someone confirm?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.