Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.