=IF(CONTAINS 2 Red Harvey balls they status changes to "red".

Options

Hello! It's me again,

Is it possible to have a formula that the Harvey Ball in "Overall Status" changes depending on the other 4 rows?

IF I have 4 Green Harvey Balls the "Overall Status" is GREEN

IF I have 3 Green Harvey Balls the "Overall Status" is YELLOW

IF I have 2 Green Harvey Balls the "Overall Status" is RED

I have this formula:

=IF(CONTAINS("Red", Status3:Status6), "Red", IF(CONTAINS("Yellow", Status3:Status6), "Yellow", "Green"))

But it only works with one ball, so if it contains one red ball then the "Overall Status" is red, and I don't want that.

Please help, Thank you so much!

Tags:

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Indira Buike

    I hope you're well and safe!

    Try something like this.

    =IF(COUNTIF(Status3:Status6, "Green") = 4, "Green", IF(COUNTIF(Status3:Status6, "Green") = 3, "Yellow", IF(COUNTIF(Status3:Status6, "Green") = 2, "Red", "")))

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Indira Buike

    I hope you're well and safe!

    Try something like this.

    =IF(COUNTIF(Status3:Status6, "Green") = 4, "Green", IF(COUNTIF(Status3:Status6, "Green") = 3, "Yellow", IF(COUNTIF(Status3:Status6, "Green") = 2, "Red", "")))

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Indira Buike
    Indira Buike ✭✭
    edited 09/15/22
    Options

    Hi @Andrée Starå

    I got it to work if i added "Red" as [false_value], Like this:

    =IF(COUNTIF(Status3:Status6, "Green") = 4, "Green", IF(COUNTIF(Status3:Status6, "Green") = 3, "Yellow", IF(COUNTIF(Status3:Status6, "Green") = 2, "Red", "Red")))

    I'm not sure why, I guess because it doesn't follow exactly the order.

    What i want it to do is that in case that they're more than 2 RED balls, the "Overall Status" is RED regardless if the other 2 balls are yellow or green.

    Also,

    I have another sheet that uses a similar formula.

    =IF(COUNTIF([Status Report]:[Status Report], "Green") = 4, "Green", IF(COUNTIF([Status Report]:[Status Report], "Green") = 3, "Yellow", IF(COUNTIF([Status Report]:[Status Report], "Green") = 2, "Red", "Red")))

    But this uses the whole column and basically if it finds more than 3 red balls in the column [Status Report] I want the ball to turn RED, if the column has up to 3 YELLOW Balls I want it to be a YELLOW Ball, and if there's less than 3 red balls or yellow balls then the ball would be GREEN.

    Is it possible that the formula can use the ">= 3" instead of only " = 3"??

    Thank You Andree, you've been really helpful!

  • Indira Buike
    Options

    Hi @Andrée Starå, I'm so sorry to bother you,

    But I got another question,

    I'm trying to make the same formula as before, but this time it has to count the Harvey balls that are in different cells, so I'm not sure how to make the range work in the =COUNTIF function, I tried using the "+" but just got "unparseable", it would be the same idea as before.

    IF I have 5 Green Harvey Balls the "Project Testing The Kit" is GREEN

    IF I have 3 or more Yellow Harvey Balls the "Project Testing The Kit" is YELLOW

    IF I have 1 Red Harvey Ball the "Project Testing The Kit" is RED

    Thank you for your help!

  • cghallo_WBD
    Options

    Hello, @Andrée Starå! I tried following the above for a more scaled down version where if Budget Health, Risk Health, Schedule Health or Scope Health are red, then overall health=red (and same for yellow) using the below and turning it into a column formula but it's not returning. Any thoughts?

    =IF(COUNTIF([Budget Health]:[Scope Health], "Red", IF([Budget Health]:[Scope Health], "Yellow"), "Yellow", "Green")))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!