Parent row summary status

Haley Wampner

I am trying to get the status of the parent row formulated to reflect a summary of the child rows:

Not Started - should be reflected only when all child rows show "Not Started"
In Progress - should be shown when a single child row shows "In Progress"
Complete - any combination of the remaining statuses should provide a "Complete" parent status.

Submitted to QC
Resubmitted to QC
Approved
Cleared
Deviation
Escalated

N/A -should not be excluded from all logic and treated as a non-factor.

This is what I have thus far, but I don't even know how to begin to exclude N/A from affecting my results. Every aspect of this formula works from the overall IF to each individual IF/COUNTIF, with the exception of the initial COUNTIFS not calculating the correct total (result is 0 in a completely applicable hierarchy or 4 or more).

=IF(COUNTIFS(CHILDREN(), "COMPLETE", CHILDREN(), "SUBMITTED TO QC", CHILDREN(), "RESUBMITTED TO QC", CHILDREN(), "APPROVED", CHILDREN(), "CLEARED", CHILDREN(), "DEVIATION", CHILDREN(), "ESCALATED") = COUNT(CHILDREN()), "COMPLETE", IF(COUNTIF(CHILDREN(), "NOT STARTED") = COUNT(CHILDREN()), "NOT STARTED", IF(COUNTIF(CHILDREN(), "IN PROGRESS") > 0, "IN PROGRESS", "WORKS")))

Haley Wampner

Broke up COUNTIFS into a sum of multiple COUNTIF functions and it works

=IF((COUNTIF(CHILDREN(), "COMPLETE") + COUNTIF(CHILDREN(), "SUBMITTED TO QC") + COUNTIF(CHILDREN(), "RESUBMITTED TO QC") + COUNTIF(CHILDREN(), "APPROVED") + COUNTIF(CHILDREN(), "CLEARED") + COUNTIF(CHILDREN(), "DEVIATION") + COUNTIF(CHILDREN(), "ESCALATED")) = COUNT(CHILDREN()), "COMPLETE", IF(COUNTIF(CHILDREN(), "NOT STARTED") = COUNT(CHILDREN()), "NOT STARTED", IF(COUNTIF(CHILDREN(), "IN PROGRESS") > 0, "IN PROGRESS", "NOT STARTED")))

Haley Wampner

Incorporated the sum of multiple COUNTIF functions to include N/A in each parameter. Formula now serves its entire purpose. Final product:

=IF((COUNTIF(CHILDREN(), "COMPLETE") + COUNTIF(CHILDREN(), "SUBMITTED TO QC") + COUNTIF(CHILDREN(), "RESUBMITTED TO QC") + COUNTIF(CHILDREN(), "APPROVED") + COUNTIF(CHILDREN(), "CLEARED") + COUNTIF(CHILDREN(), "DEVIATION") + COUNTIF(CHILDREN(), "ESCALATED") + COUNTIF(CHILDREN(), "N/A")) = COUNT(CHILDREN()), "Complete", IF((COUNTIF(CHILDREN(), "NOT STARTED") + COUNTIF(CHILDREN(), "N/A")) = COUNT(CHILDREN()), "Not Started", IF((COUNTIF(CHILDREN(), "IN PROGRESS") + COUNTIF(CHILDREN(), "N/A")) > 0, "In Progress", IF((COUNTIF(CHILDREN(), "COMPLETE") + COUNTIF(CHILDREN(), "SUBMITTED TO QC") + COUNTIF(CHILDREN(), "RESUBMITTED TO QC") + COUNTIF(CHILDREN(), "APPROVED") + COUNTIF(CHILDREN(), "CLEARED") + COUNTIF(CHILDREN(), "DEVIATION") + COUNTIF(CHILDREN(), "ESCALATED") + COUNTIF(CHILDREN(), "N/A")) < COUNT(CHILDREN()), "In Progress", "Not Started"))))