COUNTIF Formula Help

Dane Sheldon

Hello,

In my project plan document I am trying to automate the status of the parent row based on the status of the children. The options are "Not Started", "In Progress" or "Complete". Currently, using the below formula it updates the Parent status correctly except in instances where 1 or less than all of the children are "Not Started". What I want is for the Parent status to list as "In Progress" unless all children are listed as "Not Started"

Current Formula:

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") >= 1, "In Progress", " ")))

Attempt to update per above. Currently coming back unparseable:

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") >= 1, "In Progress", IF(COUNTIF(CHILDREN(), “Not Started”) <= COUNT(CHILDREN()), “In Progress”))))

Mark Safran

Hi Dane,

Try this out:

=IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNT(CHILDREN()) <> 0), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress "))

Parent will only be "Complete" if all children are "Complete".

Parent will be "Not Started" if children are all "Not Started" or blanks

Otherwise, parent will be "In Progress"

-MS