# Match Cell Text in one or more cells to give result

#### november_whiskey

##### New Member
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.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Special-K99

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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?

Last edited:

#### november_whiskey

##### New Member
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

##### Well-known Member
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

##### New Member
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.

Replies
0
Views
154
Replies
3
Views
400
Replies
2
Views
479
Replies
4
Views
236
Replies
4
Views
600

1,195,903
Messages
6,012,206
Members
441,681
Latest member
AkosiJessica

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back