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

John Knipper

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

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. "

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?

"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. "