Conditional formatting based on text status of 3 cells

[Sandy82]

Hi

I have a spreadsheet where 1 cell is conditionally formatted based on the
text status of 3 other cells, the text options being good, bad or average.
The criteria is a follows:

If more than 2 goods the cell = green
if more than 2 bads the cell = red
If more that 2 averages or a mixture of all three the cell = amber

I would be very grateful if you could give me any help on the formula
required?

 

[T. Valko]

If you have 3 cells, then:

If more than 2 goods the cell = green
if more than 2 bads the cell = red

If more than 2 really means: if *all* 3 cells contain... Right?

Try this...

Assume you want to format D1 based on the contents of A1:C1.

Select cell D1
Goto the menu Format>Conditional Formatting
Condition 1 - GREEN
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="good")
Click the Format button
Select the Patterns tab
Select a nice shade of GREEN
OK
Click the Add button
Condition 2 - RED
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="bad")
Click the Format button
Select the Patterns tab
Select a nice shade of RED
OK
Click the Add button
Condition 3 - AMBER
Select the Formula Is option
Enter this formula in the box on the right:
=COUNTA(A1:C1)=3
Click the Format button
Select the Patterns tab
Select a nice shade of AMBER (YELLOW ?)
OK out

 

[Sandy82]

I'm sorry I didn't explain myself properly

If 2 or more cells are good cell = green
if 2 or more cells are bad cell = red

the last option is if 2 or more cells are average or, if cells are 1 of each
i.e.
1 good, 1 bad and 1 average cell = amber

Regards
Sandy 82

 

[T. Valko]

OK, use these formulas:

GREEN:

=COUNTIF(A1:C1,"good")>=2

RED:

=COUNTIF(A1:C1,"bad")>=2

AMBER:

Use the same formula *if all 3 cells* will contain any combination other
than those above.

=COUNTA(A1:C1)=3

 

[Sandy82]

Thats great, thank you so much for your help

OK, use these formulas:

GREEN:

=COUNTIF(A1:C1,"good")>=2

RED:

=COUNTIF(A1:C1,"bad")>=2

AMBER:

Use the same formula *if all 3 cells* will contain any combination other
than those above.

=COUNTA(A1:C1)=3

 

[T. Valko]

You're welcome. Thanks for the feedback!