Conditional formatting based on values in A1:A6

[kpierce]

Hello -
I have a spreadsheet where A1 thru A6 have values of R,Y,G,N/A
Example:
A1 = R
A2 = Y
A3 = G
A4 = Y
A5 = G
A5 = Y

I want to create a formula that will automatically update B1 based on the following criteria:

If there are 2 or more Y, then B1 will be Y
If there are 2 or more R, then B1 will be R
otherwise B1 will be G

If have messed around with COUNTIF, but have had no luck. Is there an easy way to put this into a formula?

thanks in advance for all the help.

 

[T. Valko]

Hello -
I have a spreadsheet where A1 thru A6 have values of R,Y,G,N/A
Example:
A1 = R
A2 = Y
A3 = G
A4 = Y
A5 = G
A5 = Y

I want to create a formula that will automatically update B1 based on the following criteria:

If there are 2 or more Y, then B1 will be Y
If there are 2 or more R, then B1 will be R
otherwise B1 will be G

If have messed around with COUNTIF, but have had no luck. Is there an easy way to put this into a formula?

thanks in advance for all the help.

What should happen if there are 2 Y and 2 R (if that's even a possibility)?

 

[kpierce]

if there are 2 RED and 2 YELLOWs then it needs to be RED.

This is for a simply dashboard board so they colors progress from Green, Yellow, Red so if there are Reds that trumps Yellow...

I hope that makes sense.

thanks

 

[T. Valko]

if there are 2 RED and 2 YELLOWs then it needs to be RED.

This is for a simply dashboard board so they colors progress from Green, Yellow, Red so if there are Reds that trumps Yellow...

I hope that makes sense.

thanks

Try this...

=IF(COUNTIF(A1:A6,"R")>=2,"R",IF(COUNTIF(A1:A6,"Y")>=2,"Y","G"))

 

[kpierce]

Great and Thank you.

 

[T. Valko]

Great and Thank you.

You're welcome. Thanks for the feedback!

 

[kpierce]

I am going to post one more question... I hope everyone does not feel I am becoming a pain...

thanks in advance.

 

[kpierce]

Just realized there is a wrinkle in my logic. If there is atleast one R and atleast one Y, then it needs to be Y, right now it stays G..

Any thoughts?

 

[T. Valko]

Just realized there is a wrinkle in my logic. If there is atleast one R and atleast one Y, then it needs to be Y, right now it stays G..

Any thoughts?

Do you mean OR...

...If there is at least one R OR atleast one Y...

Or, do you really mean AND?

 

[kpierce]

Correct AND.