A formula to count the colors in excel sheet

[MFT]

Dear All,

I require a formula to count the respective colours for each employee as below:

 

[James006]

Hi,
As long as each color has its pre-defined letter or symbol, it will be easier for you to use Countif() with this unique identifier ...

 

[MFT]

Hi James,

That I have put manually for convenience to count. However in the original file there are no letters or symbols.

 

[awoohaw]

@*MFT, What are the rules that place the colors in those cells?

What we are saying is that there is not a formula that reads cell colors. If there is, it is something I am not aware of.

 

[Hyakkivn]

You can't count color in excel. Just put some letter in it then use Countif

 

[James006]

Hello again,

To count colors, you can adapt following UDF

Function mycount(rng As Range)
'Example for Red =mycount(C10:AG10)
Dim c As Range, counter As Long
    For Each c In rng
        If c.Interior.Color = 255 Then counter = counter + 1
    Next c
 mycount = counter
End Function

 

[James006]

Version 2 ...

Function mycount(rng As Range, mycolor As Range)
' Version 2 for all colors
' Example for Cell E16 =mycount($C10:$AG10,E$15)
Dim c As Range, counter As Long
    For Each c In rng
        If c.Interior.Color = mycolor.Interior.Color Then counter = counter + 1
    Next c
 mycount = counter
End Function