Nested IF, COUNTIF and OR statements

[Kotche]

I am attempting to build a simple dashboard that will reflect the overall status of several processes with a Red, Amber, Green indicator.

For example, I have 6 processes in total, each to be marked as Red, Amber or Green.

If all 6 are marked as Green, I want my Overall Status to be marked Green.

If one or more are marked as Amber, I want my Overall Status to be marked Amber.

However, if one or more are marked as Red, I want my Overall Status to marked Red.

My formula returns my expected Overall Status if there is only two types of status among the 6 processes (ie. 4x Green and 2x Amber returns me an Overall Status of Amber). But if I have a mixture of all three statuses among the 6 processes (ie. 4x Green, 1x Amber and 1x Red), I get an Overall Status of Amber instead of Red.

Where have I gone wrong with my formula to provide my Overall Status?

=IF(((COUNTIF(F8:F13,"Green"))=6),"Green",(IF(COUNTIF(F8:F13,"Amber")>=1,"Amber","Red")))

I'm thinking I need an OR statement in my second COUNTIF statement ...?

 

[BrianJN1]

Re: Help using nested IF, COUNTIF and OR statements

Does modifying your formula to:
=IF(((COUNTIF(F8:F13,"Green"))=6),"Green",(IF(COUNTIF(F8:F13,"Red")>=1,"Red","Amber")))
comply with everything you need? It at least works for the single red single amber state.

If all are Green we have no problem but if Red must be you next immediate consideration then it needs to be the next in the "loop" of concerns. "Amber" is therefore your default.

 

[Kotche]

Re: Help using nested IF, COUNTIF and OR statements

Brilliant, thank you. That does the trick and seems obvious now. I appreciate your fresh set of eyes!

 

[BrianJN1]

Re: Help using nested IF, COUNTIF and OR statements

You're welcome.