Help with Parent/Child and Sum Formula

SteveE

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?

KPH

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!