Overall Health based on not completed tasks

raisy27

I am looking to update this formula I currently use in the parent of the Health column.

It currently looks at child rows below and returns the color of the most "severe" status. (if it find any red, it returns red)

What I need:
However, if the phase is completed and the rest of the tasks are on track, I don't want to report completed phases health in the over all parent health.

Module 1 Health should represent the most severe health for all non-completed children.

Current Formula I need to modify to exclude checked rows:

=IF(COUNT(CHILDREN([Module - Phases]@row)) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray"))))

Can you help me modify my formula? I am looking to exclude any children rows in the count where checkbox is checked under "Completed"

Brian_Richardson

Don't use Completed@row, use CHILDREN(Completed@row) instead. You have to give COUNTIFS the same size range for each argument. By telling it to check all the children for Red, but then check only the single row for Completed, it's erroring out.

So:

=IF(COUNT(CHILDREN([Module - Phases]@row)) > 0,

IF(COUNTIFS(CHILDREN(), "Red", CHILDREN(Completed@row), 0) > 0, "Red",

IF(COUNTIFS(CHILDREN(), "Yellow", CHILDREN(Completed@row), 0) > 0, "Yellow",

IF(COUNTIFS(CHILDREN(), "Green", CHILDREN(Completed@row), 0) > 0, "Green", "Gray"))))

Adam Murphy

Change to COUNTIFS and add additional criteria for the Completed@row, 0 (0 is unchecked, 1 is checked). Hope this helps!

raisy27

Thanks ! I must be missing something as I get incorrect argument set

=IF(COUNT(CHILDREN([Module - Phases]@row)) > 0, IF(COUNTIFS(CHILDREN(), "Red", Completed@row, 0) > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow", Completed@row, 0) > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green", Completed@row, 0) > 0, "Green", "Gray"))))

Brian_Richardson

Don't use Completed@row, use CHILDREN(Completed@row) instead. You have to give COUNTIFS the same size range for each argument. By telling it to check all the children for Red, but then check only the single row for Completed, it's erroring out.

So:

=IF(COUNT(CHILDREN([Module - Phases]@row)) > 0,

IF(COUNTIFS(CHILDREN(), "Red", CHILDREN(Completed@row), 0) > 0, "Red",

IF(COUNTIFS(CHILDREN(), "Yellow", CHILDREN(Completed@row), 0) > 0, "Yellow",

IF(COUNTIFS(CHILDREN(), "Green", CHILDREN(Completed@row), 0) > 0, "Green", "Gray"))))