Formula to sum Children if a Helper Column for Parent Row is checked

Options

I've created a Helper Column that checks itself if it is a Parent Row. I'd like to create a column formula that will automatically sum any children under that Parent Row. Thanks in advance!

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/07/24
    Options

    Try a column with this formula

    =IF([Helper Column2]@row =1,SUM(CHILDREN([# of Estimated Hours]@row)))

    And a Second one with this.

    =IF([Helper Column2]@row =1,SUM(CHILDREN([Actual Hours]@row)))

    Otherwise you would have to enter =Sum(Children()) on each parent row if you want to keep the children rows editable.

    If you found this comment helpful. Please respond with the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, and accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Sandy Glassberg
    Options

    Thank you, Mark.poole, but that resulted in "unparseable". Any other suggestions?

  • PCG Sam Harwart
    PCG Sam Harwart ✭✭✭✭
    edited 05/07/24
    Options

    Hi @Sandy Glassberg ,

    You won't be able to have a column formula and manual entry in the same column. You could have a column that allows for entering in child task hours and a second column that totals the children. Conditional Formatting could also gray out the parent rows so users know not to enter data there.

    Something like this:

    [Estimated Hours - Parent] =IF(C@row = 0, "", SUM(CHILDREN([Estimated Hours - Child]@row)))

    [C] =COUNT(CHILDREN())

    Conditional Formatting for rows with C>0.

    Does this help?

    Cheers,
    Sam

    -
    primeconsulting.com | Smartsheet's 2023 Partner of the Year for North America
    Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!