Help with Parent/Child and Sum Formula
Overview: I have a project plan (below) with a column for the user to enter remaining work effort/hours to complete a task. We also have milestones (checked tasks Main Task A, B and C in example). It is also possible for the users to enter additional tasks, so I will need to be able to capture new tasks as well.
Objective: Sum the work effort remaining for the entire project, with subtotals for parent tasks (in my example – Project Plan, Main Task A, Subtask 1, Subtask 2, Main Task B and Main Task C). Hoping to get the results I typed into the OBJECTIVE column.
I have been playing around and added a column formula called Fixed, which just copies what the user enters in the remaining work effort column. By adding additional helper columns (SUM, Total and Project Remaining) I believe I am able to get most, but not all the various totals I need, but it sure is ugly looking.
Is there a better/easier way?
Best Answers

Yes!
And, better still you can get all of the numbers with none of the extra columns!
This formula:
=SUM(DESCENDANTS([Remaining Work Effort]@row))
Gives you the sum of all descendant rows.
It gets most of the data you need. But it does not deliver the lowest level.
So we can wrap it in an IF.
IF a row has more than 0 children then we use the DESCENDANTS formula.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, SUM(DESCENDANTS([Remaining Work Effort]@row)))
Which just gives you what we had:
But we can now add a little part at the end, for IF the COUNT of CHILDREN is not greater than 0. In that case, we just need their Remaining Work Effort.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, SUM(DESCENDANTS([Remaining Work Effort]@row)), [Remaining Work Effort]@row)
You can add rows in between and the sums will update.
Magic!

If you can, please mark this thread as answered, to help others searching for something similar (and so I know you're all good!).
Answers

Yes!
And, better still you can get all of the numbers with none of the extra columns!
This formula:
=SUM(DESCENDANTS([Remaining Work Effort]@row))
Gives you the sum of all descendant rows.
It gets most of the data you need. But it does not deliver the lowest level.
So we can wrap it in an IF.
IF a row has more than 0 children then we use the DESCENDANTS formula.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, SUM(DESCENDANTS([Remaining Work Effort]@row)))
Which just gives you what we had:
But we can now add a little part at the end, for IF the COUNT of CHILDREN is not greater than 0. In that case, we just need their Remaining Work Effort.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, SUM(DESCENDANTS([Remaining Work Effort]@row)), [Remaining Work Effort]@row)
You can add rows in between and the sums will update.
Magic!

That is exactly what I was looking for! I did not know about descendants.
Thank you so much!!!

Glad I could help @SteveE

If you can, please mark this thread as answered, to help others searching for something similar (and so I know you're all good!).
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!