I need help with the formula for changing the overall status symbol based on other status symbol.

sweta22

I need help with the formula in summary sheet. I need to get overall status symbol color changed based on 4 status symbols.

i.e., I have Schedule Status as: Red

Risk Status: Green

Issue Status: Yellow

Change Status: Red

For the Overall Status, I need Green, if all status green. Yellow: 1 or more status is yellow, Red: 1 or more status is red.

In this case it should show red since, I have two red status symbols.

Paul Newcome

Using the OR statement means you need to rearrange the order of the colors the way I have in my last example.

Paul Newcome

So really you want it to go in the opposite order. Red if there is at least one red, yellow if there is at least one yellow, green if there are no yellows or reds?

=IF(COUNTIFS({Status Column}, @cell = "Red")> 0, "Red", IF(COUNTIFS({Status Column}, @cell = "Yellow")> 0, "Yellow", "Green")

sweta22

Yeah. I get it @Paul Newcome , but I have 4 different fields for the Status in the summary fields. Based on these 4 fields I want to get overall status field.

Issue Status: Yellow

Change Status: Red. Is there a way to get this?

Paul Newcome

Sorry about that. I read "summary sheet" and thought you were using a separate sheet for your metrics.

Using Sheet Summary fields would require an OR function in place of your COUNTIFS.

=IF(OR([Schedule Status]# = "Red", [Risk Status]# = "Red", [Issue Status]# = "Red", [Change Status]# = "Red"), "Red", IF(OR(..........), "Yellow", "Green")

sweta22

I am still not getting this by using the formula.

The requirement is :

If I use : =IF(OR([Change Status]# = "Green", [Issue Status]# = "Green", [Risk Status]# = "Green", [Schedule Status]# = "Green"), "Green",IF(OR([Change Status]# = "Yellow", [Issue Status]# = "Yellow", [Risk Status]# = "Yellow", [Schedule Status]# = "Yellow"), "Yellow","Red"))

Then it is showing "Green".

But if I use: =IF(AND([Change Status]# = "Green", [Issue Status]# = "Green", [Risk Status]# = "Green", [Schedule Status]# = "Green"), "Green",IF(AND([Change Status]# = "Yellow", [Issue Status]# = "Yellow", [Risk Status]# = "Yellow", [Schedule Status]# = "Yellow"), "Yellow","Red"))

It is showing "Red", But I am not sure how to show "1 or more of yellow/red" in thia formula.

Hope, it makes sense!

Paul Newcome

Using the OR statement means you need to rearrange the order of the colors the way I have in my last example.

sweta22

Thanks a lot for this Paul. This works!

Paul Newcome

Happy to help. 👍️