• Counting Colours in Excel •

Hi there

Chip Pearson's method is the best - and the one I would have pointed you towards had you not already found it.

However, if it's just a simple count by colour you need, then you could try this previous version of Chip's function. It doesn't include any other functions - just the simple count. And you don't need to download the module. I've used this in the past and it works just fine.

From Excel, press Alt+F11 to open the VBE. Add a new module and copy and paste the following into the module

Function CountByColor(InRange As Range, _
	WhatColorIndex As Integer, _
	Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim rng As Range
Application.Volatile True

For Each rng In InRange.Cells
If OfText = True Then
	CountByColor = CountByColor - _
			(rng.Font.ColorIndex = WhatColorIndex)
Else
	CountByColor = CountByColor - _
	   (rng.Interior.ColorIndex = WhatColorIndex)
End If
Next rng

End Function

This is the function that does the counting.

Of course, you need to know the index number of each colour. To find the number, copy this code and run it on another sheet

Sub ListColours()
Dim x As Integer

For x = 1 To 56
	ActiveSheet.Cells(x, 1).Interior.ColorIndex = x
Next
End Sub

This will list all the colours available - just read off the colour number against the row number. For example, yellow = 6.

Now you can insert the formula in your total row
=CountByColor(C1:C5,6,FALSE) or use Insert Function to choose from the User Defined List.

The function should re-calculate automatically as it uses Application.Volatile - you'll probably need to click in another cell to ensure it actually does it.

Any problems just post back.

Hi I'm not aware of any way of doing that. Perhaps you would need to put the functions in a VBA routine and then output to the sheet. Because counting colours is not a native Excel function, the one I posted earlier, and the others on Chip Pearson's site are a way around Excel's limitations. The same applies for your second question - even if you were using code to do this, you would need the colorindex value - which you would have to include in your code. So any changes would require code changes. I appreciate it's not very efficient. An alternative would be to look again at the way your data is presented. Is a coloured cell the only way? The beauty of Excel is that there are often many different ways to reach the same result.

i dont know, but i cant make it work… it always shows error in formula…. tired of trying… why is that?… can someone help me , please?……

Hi and welcome. Can you give me some more information? Where is the error? What error message do you receive? The more information you provide, the better chance we have of working out a solution.

it says: 'the formula you typed contains an error.' but i think i did everything correctly: Alt + F11, then the code, saving with Alt + Q, then typing formula and it doesnt recognize…. i have tried different codes (i found on the internet), differen formulas, but nothing…. it always shows error… thanks.

Hi That often indicates a missing bracket, or a bad reference or a missing comma etc. Can you post the formula you have that is showing the error?