Problem with Health Column Summary Formula

06/30/21
Accepted

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 [email protected] 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?

Best Answer

  • Accepted Answer

    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

Answers

  • Mark CronkMark 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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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"

  • Hi @Mark Cronk ,

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

  • Mark CronkMark 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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • edited 08/16/21

    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

  • Accepted Answer

    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

Sign In or Register to comment.