Originally Posted by Ben McClave

Hello,

This function worked for me with some test data. To use it, paste the code below to a new module and then enter the formula on a cell in your worksheet with this format:

"=CountFromColumn(CountRange, CriteriaRange,Criteria1, Criteria2)"

For example, if your components were in range A1:A10 and your outage identifier was in range B1:B10, you would write:

"=CountFromColumn(A1:A10, B1:B10, "Refurbish Actuator", "Diagnostic Testing")"

Here is the code (adapted from a post at http://www.mrexcel.com/forum/excel-q...s-range.html):

Function CountFromColumn(CountRange As Range, CriteriaRange As Range, _
Criteria1 As String, Criteria2 As String)
Dim oChecked As Object
Dim lCount As Long
Dim rCell As Range

Set oChecked = CreateObject("scripting.dictionary")
lCount = 0

With oChecked
.comparemode = 1
For Each rCell In CountRange
If Not .exists(rCell.Value) Then
.Add rCell.Value, Nothing
If _
((WorksheetFunction.CountIfs(CountRange, rCell, _
CriteriaRange, Criteria1) 0) And _
(WorksheetFunction.CountIfs(CountRange, rCell, _
CriteriaRange, Criteria2) 0)) Then
lCount = lCount + 1
End If
End If
Next
End With
CountFromColumn = lCount
End Function