Using the "parent" function for a formula

How do I write a formula to total up a percentage using the parent row?

I want to count the # of 100% complete, in the % complete column, if the parent row is equal to a location name (ATL 1)

I would be referencing another sheet to do this.

I think the #no match arguments might be causing this not to work?


Answers

  • Hi @JennS_

    You're correct! If a formula is returning an error and you try to reference those cells, you'll see an error in that second formula as well.

    What's the formula you're using with the NO MATCH? Try wrapping an IFERROR around it:

    =IFERROR(formula, "")

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You may also run into a problem in that you cannot use cross sheet references with hierarchy based functions such as PARENT.


    You will need to use a helper column on the source sheet to bring the parent row data down onto every child row and then leverage this helper column in your formula.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • JennS_
    JennS_ ✭✭✭

    Thanks for the feedback. I am struggling with how to write the formula for this. I have tried other ways of gathering this data, but I have to collect different data points, so its been hard.

    If I used a helper column in my source sheet, I think it is even more complicated as it is a form pulling (what will be) thousands of data points. Could I use a helper column in this sheet instead, and just pull the data using the parent row that way?

    For example, count the "% Complete" if 100%, and if the Category is on the parent row "ATL 1"

    The #NO MATCH function is because that data is being pulled from a form and those items have not been filled out yet. I tried to add the =IFERROR but it gave me an incorrect argument response.

    The 2 formulas that are #NO MATCH are:

    =SUMIF({Cabin Existing Conditions (Fleet Number)}, [Fleet No]@row, {Cabin Existing Conditions (Total Q's Completed)}) / [Helper % Complete]@row

    =IF(INDEX({Cabin Existing Conditions (Cabin Type)}, MATCH([Fleet No]@row, {Cabin Existing Conditions (Fleet Number)}, 0)) = "1B", 68, IF(INDEX({Cabin Existing Conditions (Cabin Type)}, MATCH([Fleet No]@row, {Cabin Existing Conditions (Fleet Number)}, 0)) = "2B", 73, IF(INDEX({Cabin Existing Conditions (Cabin Type)}, MATCH([Fleet No]@row, {Cabin Existing Conditions (Fleet Number)}, 0)) = "1B-A", 68)))


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The helper column would need to go on the source sheet. The formula for that would look something like:

    =IF(COUNT(CHILDREN([Column Name]@row)) = 0, PARENT([Column Name]@row))


    Then to get the count of how many are 100% complete and "ATL 1" you would use

    =COUNTIFS({Source Sheet % Complete}, @cell = 1, {Source Sheet Helper Column}, @cell = "ATL 1")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • JennS_
    JennS_ ✭✭✭

    @Paul Newcome Thanks for this formula. It worked perfectly!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!