Create a count of Cells of different colors

[mv816700]

I am attempting to count the number of cells in column A that are formatted with different colors (Green, Yellow, Blue, Orange, empty, etc.) and place the total in column M.

I have tried =countIf( ...) and many others.

Is there a Filter command I can use?

Any advice is welcome. I thought this would be an easy task. My bad

Thank you
Mike

 

[offthelip]

I have written my own function to do exactly this, you can use it just like any other excel macro in any workbook you install it in:

Function SumByColor(CellColor As Range, rRange As Range)
Application.Volatile
Dim cSum As Double

Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function

in use:

=sumbycolor($K2,$G$9:$G$65)

where K2 is a colored cell and G9 to G65 has some cells with the same color as K2

 

[offthelip]

I have just realised you wanted a count not the sum the function can be easily modifed :

Function countByColor(CellColor As Range, rRange As Range)
Application.Volatile
Dim cSum As Double

Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = cSum + 1
End If
Next cl
countByColor = cSum
End Function

 

[mv816700]

Wow. thx, I thought there was a way to do this using Excell. Nice thank you

but
How do I add this function? newbie to this.

 

[offthelip]

I suggest you read this MS article about User defined functions ( which is what my code is )
Create custom functions in Excel