Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


share|improve this question

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
        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


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:

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .