count w/colorfunction and two criteria

[threed]

I am attempting to count w/two criteria. A1:A20 = shaded cells/B1:B20 = text values. I have the correct macro to make the colorfunction formula work on its own, but am having difficulty making it work with two criteria. (C1 = shade criteria, D1 = text criteria)

This is what I have now:

=SUMPRODUCT(--(colorfunction(C1,A1:A20,FALSE)),--(B1:B20=D1))

Suggestions?

Thanks!

 

[Norie]

It might help to see the code for colorfunction.

What does that function actually return?

And what problems are you actually having?

Errors? Incorrect results?

 

[threed]

Thanks for the assistance...

The formula currently results in #VALUE!

The colorfunction formula will count the number of cells in a range that are shaded the identical color to a specified cell...and the formula works just fine outside of this issue.

The VBA is as follows (sorry for not having the box plugin on my work computer):


Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

Application.Volatile

Dim rCell As Range

Dim lCol As Long

Dim vResult


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