Count IF Cell contains text AND cell colour is ....

[ktoon]

Hi,

I am trying to count data from Table 1, in Table 2 - but I need to only CountIF the cell contains the specific text (FRED,GEORGE etc.) AND a specific colour as shown.

Table 1

	*RED*	*GREEN*
1:00pm	FRED	GEORGE
2:00pm	DAVE	DAVE

<tbody>
</tbody>

Table 2

	*RED*	*GREEN*
FRED	1	0
GEORGE	0	1
DAVE	1	1

<tbody>
</tbody>

At the moment I have am using this VBA:

Function CountCcolor(range_data As Range, criteria As Range) As Long    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function

With this formula:

=CountCcolor(DeliveryLog!$B$3:$F$17,Data!$B$1)

But I have not idea how to use this with COUNTIF(S) or anything because you have to choose range and criteria separately.

Any ideas anyone?

(Excel 2010)

Thanks
Kyle