Countif

Topic

New Reply

I want to do a countif based on the color of a cell. At the bottom of a column, I want to know how many cells are red, how many are blue, how many are green, etc. Any ideas? I suppose I’ll have to resort to a user defined function with VBA?

Reply | Quote

Replies

Do the cells derive their colour from conditional formatting ?. If so could you base you COUNTIF on the same criteria. Otherwise a VBA procedure would be required. What do mean by cell colour, Font or Shading ?.

Andrew C

Reply | Quote

Thanks Andrew. The cells are not given their color by conditional formatting. By cell color, I’m referring to the fill color (icon of a paint bucket). I can write the VBA code if I need to but I don’t know how to create a user defined function.

Reply | Quote

I figured out how to make my own function. I just recorded a Macro and then edited its name to say Function instead of Macro. Now I’m playing with it to get it to do what I want. I don’t know if I’ll be succesful but I’ll give it my best shot.

Reply | Quote

Put the following code in a module

Function CountColors(R As Range, Col As Integer) As Integer
Dim cell As Range
CountColors = 0
For Each cell In R
If cell.Font.ColorIndex = Col Then
CountColors = CountColors + 1
End If
Next
End Function

Then via the function wizard, select user defined and you should find the function CountColors in the list. This function requires you to enter the range in which you want to count the colors and the integer value representing the color (e.g. Red = 3). See the help for colorindex to find out the colors and the integers that correspond.

Reply | Quote

Thanks a million. That worked perfectly. The only thing that strikes me funny is that the ‘cell’ value in the ‘Range’ within the For..Next loop is not B2 or C8 or whatever, but rather the number that happens to be in that cell. It works though so I’m thrilled.

Reply | Quote

Actually, I did have to make one change to your code. One line needs to read “If cell.Interior.ColorIndex = Col Then” instead of cell.Font.ColorIndex. Thanks again.

Reply | Quote

In that routine, cell is a range object. Its value property is whatever displays in the active cell in the range. If you want to see the address of the range, then you would need to look at it Address property (cell.Address).

Reply | Quote