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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!