Problem with Health Column Summary Formula

Todd Smelser

Hi all,

I have a formula counting a task's health based on status. I am now trying to summarize those results in a summary task@row using this formula

=IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Gray")))

However, with a row of unstarted tasks (grey) I get a red summary. Can someone see the error in my formula?

Todd Smelser

I believe I have it now.

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Gray"), "Gray", IF(AND(COUNTIF(CHILDREN(), "Gray") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Gray") >= COUNTIF(CHILDREN(), "Yellow")), "Gray", IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Gray")))))

Seems to work perfectly

Mark Cronk

Hi @TSmelser ,

You need to add a COUNTIF(children(), "grey"). If the is 1 non-grey row do you want the summary to be the non-grey color? If so, add as you first function:

IF(COUNT(Children())=countif(Children(), "grey", "grey")

Mark

Todd Smelser

Hi Mark,

I have been trying to work with this as the first function

IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "grey", "grey"), IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Gray"))))

But this gives me "INCORRECT ARGUMENT"

Todd Smelser

Hi @Mark Cronk ,

I realized I should have tagged you in the response above.

Mark Cronk

Sorry for the delay in responding. Try:

IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "grey"), "grey", IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Gray"))))

Work?

Mark

Todd Smelser

Hi,

Yes, it seems to, though if the tasks beneath the summary are all grey, the status still goes red, instead of grey. There must be a ">=" parameter that is overriding the syntax you had me add

Todd Smelser

I believe I have it now.

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Gray"), "Gray", IF(AND(COUNTIF(CHILDREN(), "Gray") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Gray") >= COUNTIF(CHILDREN(), "Yellow")), "Gray", IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Gray")))))

Seems to work perfectly