# count w/colorfunction and two criteria

#### threed

##### New Member
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!

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Norie

##### Well-known Member
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

##### New Member
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

Replies
6
Views
289
Replies
1
Views
283
Replies
2
Views
182
Replies
3
Views
86
Replies
1
Views
697

1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back