Count only based on the same cell colour

[TB_Simonsen]

I am trying to establish whether there is a excel formula which will allows you to just to count the cell with the same cell colour. I have made the below listed example to illustrate what I am trying to achieve:

A B C D E

11/03	12/03	13/03	14/03	15/03	Total
Yes	Yes	<COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY> </TBODY>	<COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY> </TBODY>	Yes	2
		<COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><TBODY> </TBODY>	Yes	Yes	1
Yes	Yes	Yes	Yes	Yes	0
Yes	Yes	Yes	Yes	Yes	0
Yes	Yes	Yes	Yes	Yes	0

<TBODY>
</TBODY>

In the there are total of two green colours, and one red colour to be counted.

Any assistance in thinking of a formula would be greatly welcome.

TB_Simonsen

 

[VoG]

This may help you Sum/Count Cells By Fill Or Background Color in Excel

 

[TB_Simonsen]

Dear VOG

Many thanks for your quick reply, but I have copies the VBA code into my spreadsheet and the returning value I am getting is #name?. Any solutions as to how to overcome this error text.

TB_Simonsen

 

[VoG]

Where did you place the code. You need to press ALT + F11, then Insert > Module and paste in

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
    lCol = rColor.Interior.ColorIndex
    If SUM = True Then
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        Next rCell
    Else
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
            End If
        Next rCell
    End If
   ColorFunction = vResult
End Function