Excel count coloured cells

Question

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

Answer 1

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)