Conditional Formatting Based On List Values Existence

Hello,

I would like to conditioanlly foprmat a cell to be green yellow or red based on how many unique values exist in a range of cells.

For example.... I have a list of the 11 different sectors in the stock market....

Materials
Industrials
Financials
Energy
Consumer Discretionary
Information Technology
Communication Services
Real Estate
Health Care
Consumer Staples
Utilities

any combination of this list could exist in cell range E5:E14 which is data validated to only contain values from that list.

In cell I4, I would like it to be conditionally formatted to be:
green if 5 or more unique values exist in range E5:E14
yellow if only 4 unique values exist
red if 3 or less.

Essentially, this will be a "diversification health check" if there are 5 different unique values in that range than healthy (green)

Having a hard time figuring out where to start on this. Any help would be greatly appreciated!!

If there is always going to be a list of 11 then how about

Formula:

Please Login or Register  to view this content.

Then use conditional formatting based on the result?


Just seen that you have Excel 2016 so wont have UNIQUE; try...

Formula:

Please Login or Register  to view this content.

Ctrl+Shift+Enter to set as an array. Conditional format to your hearts delight