# Excel count coloured cells

If i have 500 cells filled with red, 500 filled with green and 50 filled with yellow, is it possible to do a countif sorta formula where i will have a cell which will count the number of red cells, green cells and yellow.

thanks

-

I believe you need custom functions for that purpose. Drop this code into a module within your workbook (Open VB Editor, insert a Module, paste the code below into that module, close VB Editor.)

`````` Function COUNTCELLCOLORSIF(CellRange As Range, ColorIndex As Integer) As Long

Dim rngCell

Application.Volatile 'Thanks, Excellll!

For Each rngCell In CellRange
If rngCell.Interior.ColorIndex = ColorIndex Then
COUNTCELLCOLORSIF = COUNTCELLCOLORSIF + 1
End If
Next rngCell

End Function

Function CELLCOLORINDEX(CellRange As Range) As Integer

Application.Volatile 'Thanks, Excellll!
CELLCOLORINDEX = CellRange.Interior.ColorIndex

End Function
``````

CELLCOLORINDEX allows you to determine the integer value of the colour of a given cell. For example, to check the integer value of A1's interior colour, your formula would be

`````` =CELLCOLORINDEX(A1)
``````

You can then use COUNTCELLCOLORSIF to count how many cells in a given area have that colour. For example, if A1 contains the integer value of the colour you want to check and the cells you want to check are in B1:B150, your formula would be:

`````` =COUNTCELLCOLORSIF(\$B\$1:\$B\$150, \$A\$1)
``````
-
You can address the re-calculation issue by adding Application.Volatile at the start of your function code. – Excellll Aug 26 '11 at 18:30
Thanks - forgot about that. Code updated. – variant Aug 27 '11 at 0:42