Help Creating a formula using Children


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")


=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
    David Tutwiler Overachievers Alumni

    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")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!