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

Indira Buike

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!

Andrée Starå

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!

Andrée Starå

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!

Indira Buike

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

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

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