Parent Row Formula based on Child Row Status

Dan H

Hello,

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

Canceled

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!!!!

Dan

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