Problem with Health Column Summary Formula

Todd Smelser
Todd Smelser ✭✭✭
in Formulas and Functions

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?

Screen Shot 2021-06-30 at 11.52.34 AM.png
· Share on FacebookShare on Twitter

Best Answer

  • Todd Smelser
    Todd Smelser ✭✭✭
    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

    · Share on FacebookShare on Twitter

Answers

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


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

    · Share on FacebookShare on Twitter
  • Todd Smelser
    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"

    · Share on FacebookShare on Twitter
  • Todd Smelser
    Todd Smelser ✭✭✭

    Hi @Mark Cronk ,

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

    · Share on FacebookShare on Twitter
  • Mark Cronk
    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


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

    · Share on FacebookShare on Twitter
  • Todd Smelser
    Todd Smelser ✭✭✭
    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

    · Share on FacebookShare on Twitter
  • Todd Smelser
    Todd Smelser ✭✭✭
    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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!