Help Creating a formula using Children

Ian Abee

Hi!

I currently am experimenting with Parent and Children Hierarchy to clean up my sheets. Below is a screenshot so you can see my mock up page. The issue I am running into is when I add an additional "Project", my formulas in the columns on the right are pulling from the whole column. I am having difficulty creating the formula to limit the Task Past Completion Date and Completed Tasks to their specific children. The formulas I have now are:

=COUNTIFS([Target Completion Date]:[Target Completion Date], @cell < TODAY(), Status:Status, <>"Complete")

and

=COUNTIF(Status:Status, "Complete")

To get the % of completed tasks I am using the formula =SUM([Completed Tasks]@row / COUNTM(Status:Status))

All in all I would like to specify these formulas just so they tally the data from Project 1, Project 2, etc. and not the whole sheet.

David Tutwiler

What you'll want to do is move your formulas to the same row as your Project X (the parent) and write your formula using the CHILDREN function so that it only picks up the child rows beneath your parent. Something like:

=COUNTIFS(CHILDREN([Target Completion Date]@row), < TODAY(), CHILDREN(Status@row), <>"Complete")