Using COUNTIF children to change a Status with multi criteria

Jeffry Lusiak

Hello!

In our Timing and Action we have a formula that indicates the status of a Parent row based off the cumulative Status choices (Not Started, In Progress, Complete, N/A) of the Children Rows below it.

How do I get the formula to register the Parent Row 'Complete" when the CHILDREN Rows = both Complete and N/A

I want to account for tasks in the rows that are marked "N/A" to count as complete in the formula below:

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

David Joyeuse

Hi @Jeffry Lusiak

It depends on how you want this to operate.

Do you want to report "Complete" if all the children are N/A or all the children are "Complete" (Case 1), or do you want to return Complete if all the rows are either N/A or Complete (Case 2)? Either case will force a different formula of its own.

Case 1:

=IF(OR(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN())), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

Case 2:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

Note also that case 2 will also retun "Complete" if all the rows are either "Complete" or "N/A", but will also do it if you have 5 children and 2 are "complete" and the other 3 are "N/A".

Now, you may also want to adapt the "Not Started" status to also take into accounts rows that are N/A as well making an ultimate Case 3 scenario that will also return "Not Started" if 3 rows are N/A and 2 Not Started.:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), OR(@cell = "Not Started", @cell = "N/A")) = COUNT(CHILDREN()), "Not Started", "In Progress"))

Really up to how you want this operates :)

Hope it helped!

David Joyeuse

Hi @Jeffry Lusiak

It depends on how you want this to operate.

Do you want to report "Complete" if all the children are N/A or all the children are "Complete" (Case 1), or do you want to return Complete if all the rows are either N/A or Complete (Case 2)? Either case will force a different formula of its own.

Case 1:

=IF(OR(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN())), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

Case 2:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

Note also that case 2 will also retun "Complete" if all the rows are either "Complete" or "N/A", but will also do it if you have 5 children and 2 are "complete" and the other 3 are "N/A".

Now, you may also want to adapt the "Not Started" status to also take into accounts rows that are N/A as well making an ultimate Case 3 scenario that will also return "Not Started" if 3 rows are N/A and 2 Not Started.:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), OR(@cell = "Not Started", @cell = "N/A")) = COUNT(CHILDREN()), "Not Started", "In Progress"))

Really up to how you want this operates :)

Hope it helped!

Jeffry Lusiak

Thanks David!

Case 2 solved it!