Multiple COUNTIF statements to return a value

BobS7

Phoning a friend.. I am repurposing a sheet to be used for tracking recurring tasks. There may be activities either weekly or month is not required, so I want to mark the activity (parent) as “N/A” and not “Complete”. I have been able to adjust formula for everything except when all child status is are “N/A”. I believe I need to add another COUNTIF to the first statement that does a count of all Children that are “N/A” then Parent is “N/A”. 

Test Status – drop down for owner to update

Test Status – Formulaic is a helper column that contains the logic. It is used in a couple places on the sheet. 

Parent rows (in blue) for 1 and 3 are correct. Parent row 2 should be "N/A"

=IF([Step Number]@row = 0, IF(COUNTIF(CHILDREN([Task Status]@row), "") + COUNTIF(CHILDREN([Task Status]@row), "Not Started") = COUNT(CHILDREN(Week@row)), "Not Started",

IF(COUNTIF(CHILDREN([Task Status]@row), "Complete") = COUNT(CHILDREN(Week@row)), "Complete", IF(COUNTIF(CHILDREN([Task Status]@row), "Error") > 0, "Error",

IF(COUNTIF(CHILDREN([Task Status]@row), "In Progress") > 0, "In Progress",

IF(COUNTIF(CHILDREN([Task Status]@row), "Not Started") > 0, "In Progress", "Complete"))))),

IF([Task Status]@row = "", "Not Started", [Task Status]@row))

KPH

You could add another IF to evaluate if the COUNT of child rows with N/A is equal to the count of child rows and in that situation return "N/A". That is the part in bold here (remember the extra closing parenthesis towards the end).

=IF([Step Number]@row = 0, IF(COUNTIF(CHILDREN([Task Status]@row), "") + COUNTIF(CHILDREN([Task Status]@row), "Not Started") = COUNT(CHILDREN(Week@row)), "Not Started", IF(COUNTIF(CHILDREN([Task Status]@row), "Complete") = COUNT(CHILDREN(Week@row)), "Complete", IF(COUNTIF(CHILDREN([Task Status]@row), "N/A") = COUNT(CHILDREN(Week@row)), "N/A", IF(COUNTIF(CHILDREN([Task Status]@row), "Error") > 0, "Error", IF(COUNTIF(CHILDREN([Task Status]@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN([Task Status]@row), "Not Started") > 0, "In Progress", "Complete")))))), IF([Task Status]@row = "", "Not Started", [Task Status]@row))

It is a strange formula though.

If the Step Number is 0,

and all the child rows are Not Started or Complete or N/A it will return the same.

and if not but any child row is Error, it returns Error

and if not but any child row is In Progress or Not Started, it returns In Progress

and if not it returns Complete

If the Step Number is not 0 and the task status is blank it returns Not Started

and if not it returns the Task Status.

Is that the intention? I hope so, and I hope this extra part is what you need.

KPH

You could add another IF to evaluate if the COUNT of child rows with N/A is equal to the count of child rows and in that situation return "N/A". That is the part in bold here (remember the extra closing parenthesis towards the end).

=IF([Step Number]@row = 0, IF(COUNTIF(CHILDREN([Task Status]@row), "") + COUNTIF(CHILDREN([Task Status]@row), "Not Started") = COUNT(CHILDREN(Week@row)), "Not Started", IF(COUNTIF(CHILDREN([Task Status]@row), "Complete") = COUNT(CHILDREN(Week@row)), "Complete", IF(COUNTIF(CHILDREN([Task Status]@row), "N/A") = COUNT(CHILDREN(Week@row)), "N/A", IF(COUNTIF(CHILDREN([Task Status]@row), "Error") > 0, "Error", IF(COUNTIF(CHILDREN([Task Status]@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN([Task Status]@row), "Not Started") > 0, "In Progress", "Complete")))))), IF([Task Status]@row = "", "Not Started", [Task Status]@row))

It is a strange formula though.

If the Step Number is 0,

and all the child rows are Not Started or Complete or N/A it will return the same.

and if not but any child row is Error, it returns Error

and if not but any child row is In Progress or Not Started, it returns In Progress

and if not it returns Complete

If the Step Number is not 0 and the task status is blank it returns Not Started

and if not it returns the Task Status.

Is that the intention? I hope so, and I hope this extra part is what you need.

BobS7

Thanks for the help. Added the additional statement and is now working as expected.

Yes, the intent is to update and track the progress as the actions are completed. Knowing what is Not Started or Complete (all children have same status) is correct. N/A was added to account for weekly tasks that may be removed for one reason or another and I do not want to be showing as Not Started/Complete for actions removed.

The purpose of the Step Number is not 0 is to account for when rows are added to a parent but status was not entered. By default everything is Not Started, once they begin the activity the owner updates the status.

Greatly appreciate the help.

KPH

Sounds good! Glad I could help unstick you.