How to count no. of Cells in MS Excell depending on their Fill Colors

I want to count the no. of cells in a row or column based of their background colors, e开发者_开发百科.g. how many are Red background, how many are Blue etc in a range of cells

It looks like there are ways to do this, using Excel VBA, but not natively using the COUNTIF function. The COUNTIF function uses the data in the cell as criteria - is there a data criteria in use for determining background color that could also be used for a COUNTIF function?

You have to use VBA (open VBA editor with Alt+F11)

1. First get the index of colors by runing this Sub:

   Sub showColorIndices()
   For i = 1 To 56
       Range("A" & i).Interior.ColorIndex = i
       Range("B" & i).Value = " " & i
   Next
   End Sub
   

You'll get something like this:

2. Then you can count the number of cells of a given color index with this Function:

   Function fnNbCellsColor(Plage As Range, ColorIndex As Integer) As Long
   
   Dim rCell As Range
   
   For Each rCell In Plage
       If rCell.Interior.ColorIndex = ColorIndex Then
           fnNbCellsColor = fnNbCellsColor + 1
       End If
   Next
   
   End Function
   

To count the number of blue cells, just write this formula in your sheet:

= fnNbCellsColor(D1:D20; 5)