Parent Row Formula based on Child Row Status

Dan H
Dan H ✭✭✭
edited 03/22/23 in Formulas and Functions


I am trying to write a formula where my child row status roll up to the parent row for an auto update. My row values are listed below and how I want the logic to roll up:

Not Started - all children are “Not Started”

In Progress - all children are "In Progress", Ahead of Schedule", "Behind Schedule"

Complete - all children are “Complete” or “ Canceled”

Ahead of Schedule

Behind Schedule


Based on responses I found in the Community, I can up with the below formula but it is not working.

=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN("Not Started")), IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", “Canceled”), IF(COUNTIFS(CHILDREN(), "In Progress") = COUNT(CHILDREN()), “In Progress”. “ Ahead of Schedule”, “Behind Schedule”))

Any help would be appreciated! Thank you in advance!!!!



  • Austin Smith
    Austin Smith ✭✭✭✭✭

    The fastest way would be to make a helper column. You can't make a dropdown column a column formula and keep the dropdown which means a fair amount of copy/paste on down the road. This gives you a column that does a thing (my preference).

    I'm sure someone can simplify this.

    helper column =IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Ahead of Schedule") = COUNT(CHILDREN()), "Ahead of Schedule", IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Complete", "n/a")))))), IF(Status@row = "Not started", "Not Started", IF(Status@row = "In Progress", "In Progress", IF(Status@row = "Ahead of Schedule", "In Progress", IF(Status@row = "Behind Schedule", "In Progress", IF(Status@row = "Complete", "Complete", IF(Status@row = "Cancelled", "Complete", "n/a")))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!