Is there a formula to specify the status of parent row based on status of the children?

Sara Ross

I have a project with several milestones that I've designated as children of my main project. These milestones in turn have their own children (tasks). I would like to use a formula that would specify the parent's status based on the status of it's descendants. In particular, I would like the status to be:

1. "Not Applicable" if ALL of the descendants are designated as "Not Applicable"
2. "Completed" if ALL of the descendants are designated as "Completed" OR "Not Applicable"
3. "Past Due" if ANY of the descendants are designated as "Past Due"
4. "In Progress" if ANY of the descendants are designated "In Progress" AND none designated as "Past Due"
5. "Not Started" if ALL of the descendants are "Not Started" or "Not Applicable"

Any advice is greatly appreciated!

Heather Duff

Hi @Sara Ross

Just to confirm, "descendants" in Smartsheet means parent and grandparent rows of that row, and "children" means rows indented below the row. Do you actually want the formula to reflect the status of the descendants, or the children?

If it's actually children (which it sounds like what you want), I think this will work for you:

=if(countif(children(),"Not Applicable")=count(children()),"Not Applicable", if((countif(children(),"Completed")+countif(children(),"Not Applicable"))=count(children()),"Completed", if((countif(children(),"Not Started")+countif(children(),"Not Applicable"))=count(children()),"Not Started", if(countif(children(),"Past Due")>0,"Past Due", if(countif(children(),"In Progress")>0,"In Progress","")))))

Hope this helps. Let me know if it works!

Best,

Heather