I have the need to count the number of cells that meet several criteria, one of which is the cell's fill color. I have written a VBA script that accomplishes this task singly. I can give it a reference color cell and a range of cells to check, and it returns an array of 1 or 0 if the target cell matches (1) or doesn't match (0) the color of the reference cell.

However, when I try to combine this array UDF with the SUM function and use it as an array function (CTRL+SHFT+ENTER), it doesn't seem to be itemizing the array cell by cell in my color function.

Here's my code:

Function IsColor(rColor As Range, rColorRange As Range) As Variant

Dim rCell As Range
Dim iCol As Integer
Dim vResult()
Dim vCounter As Integer

iCol = rColor.Interior.ColorIndex

For Each rCell In rColorRange

If rCell.Interior.ColorIndex = iCol Then
vCounter = vCounter + 1
ReDim Preserve vResult(1 To vCounter)
vResult(vCounter) = 1
Else: vCounter = vCounter + 1
ReDim Preserve vResult(1 To vCounter)
vResult(vCounter) = 0

End If

Next rCell

IsColor = vResult

End Function

And here's an example of usage:

{=SUM((D1:D5="abc")*(IsColor(B3,D1:D5)))}

Where column D is:
a (no fill)
ab (no fill)
abc (yellow fill, as is B3, the reference cell)
abc (no fill)
abcde (no fill)

The sum result should be just 1 cell (D3) which is BOTH yellow and "abc". However, the actual result I get is 2. It's as if the IsColor function was returning a positive result because at least one of the cells in the range was correctly color-filled instead of checking cell by cell within the array.

Any thoughts? All help is much appreciated!