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
Best Answers
-
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.
Megan Ryzenga | Medical Education Analyst
3M Health Care, Medical Solutions Division
She/Her
-
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. "
Answers
-
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.
Megan Ryzenga | Medical Education Analyst
3M Health Care, Medical Solutions Division
She/Her
-
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").
-
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. "
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!