Excel - count cells based on color

[Rbordonaro]

Hi, this is an easy one, but I cannot find a matching thread for the answer.

Column cells conditional formatted to color fill based on content of cell, now I want to count the # of cells that are orange or green, etc. I have a key listing the colors where I want to display the totals.

Your assistance is appreciated.

Thanks,
R

[alansidman]

Since you have conditionally formatted the colors, why not use the same logic to count the cells employing either the Countif or Countifs functions within Excel.

Alan

[Rbordonaro]

honestly, I don't know how to do that - how do I idenitfy each color in my column?

[martindwilson]

you dont need to say its column a
and orange cells are coloured by cf using the rule >1 then use countif
eg countif(a1:A100,">1")

[AlKey]

Here is a vb UDF solution for you

1. Copy code below
2. Press Alt and F11 on your keyboard to open VB Editor
3. Click on Insert and select Module
4. Paste code into Module and close VB Editor.

Use formula

example:
=GetColor(A2) and drag formula down. Formula will return numeric value for each color which you can count with COUNTIF function.

Function GetColor(Mycell As Range)
GetColor = Mycell.Interior.ColorIndex
End Function

[Rbordonaro]

I think the issue I am having is because the cells I am trying to count are conditionally formatted to a specific color based on cell value. Any advice?

[AlKey]

@Rbordonaro

Did you try the solution I offered?

[alansidman]

Use the same logic that you used to conditionally format in your countif function. Martin has given you an example how to do this. What is the logic to color the cells.