Match Cell Text in one or more cells to give result

[november_whiskey]

I have six cells (C17, C21, C25, C29, C33, C37) where you can select Red, Amber or Green in each cell.

In another cell on the spreadsheet I want this cell to automatically tell me Red if one of the six cells has Red selected, Red if three of the six cells have Amber selected, Amber if 1 or 2 of the cells have Amber Selected and Green if the previous conditions do not apply.

If anyone can help I would be grateful.

 

[Special-K99]

How do you "select Red, Amber or Green in each cell" ?
Is this just purely text or are colouring the cells in those colours?

 

[november_whiskey]

Drop down list and there is a conditional format which selects a colour dependant on the selected drop down, e.g. Select "Red, the cell colour changes to Red.

 

[Special-K99]

A formula cannot determine the colour of a cell, only VBA can.
But you can use the same conditional formats of those Cxx cells to get the result you want.

What's the conditional format of those Cxx cells say (in its entirety) to turn them Red Amber Green?

 

[november_whiskey]

A formula cannot determine the colour of a cell, only VBA can.
But you can use the same conditional formats of those Cxx cells to get the result you want.

What's the conditional format of those Cxx cells say (in its entirety) to turn them Red Amber Green?

I dont want to determine by colour. I want to determine by the text in the drop down.

 

[Special-K99]

Format as green and enter these Conditional Formats:

=(COUNTIF(C17:C37,"Red")=1)
=(COUNTIF(C17:C37,"Amber")=3)
=(COUNTIF(C17:C37,"Amber")=2)

 

[november_whiskey]

Format as green and enter these Conditional Formats:

=(COUNTIF(C17:C37,"Red")=1)
=(COUNTIF(C17:C37,"Amber")=3)
=(COUNTIF(C17:C37,"Amber")=2)

Fab, this worked a treat. Thank you.