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

  • Megan Ryzenga
    Megan Ryzenga ✭✭✭
    Answer ✓

    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

  • John Knipper
    John Knipper ✭✭✭✭
    Answer ✓

    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

  • Megan Ryzenga
    Megan Ryzenga ✭✭✭
    Answer ✓

    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

  • John Knipper
    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
    John Knipper ✭✭✭✭
    Answer ✓

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!