Multiple criteria in same column

[kalkap]

I'm trying to count the number of components that meet 2 criteria fro
the same column. I've tried various iterations of COUNTIFS but no luck
Column A contains a list of components in which there are numerou
duplicates. Column B contains a maintenance outage identifier. Column
contains maintenance tasks (duplicate components because numerou
maintenance items). I'm trying to counter the number of components tha
fall in the same outage identifier where "Refurbish Actuator" will b
performed AND of these components also will "Diagnostic Testing" done.
"Refurbish Actuator" and "Diagnostic Testing" are within the same colum
which is where my troubles come into play.

Hopefully this is clear enough to get some help. Thanks in advance

 

[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-...te-userform-combobox-unique-values-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

 

[kalkap]

Sorry so late getting back. I was away for a bit. Thanks for th
feedback. I will try it out and get back to you. Thanks again

 

[kalkap]

Hello,

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

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

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

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

Here is the code (adapted from a post at http://tinyurl.com/d3w9v4h

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

I feel like this is on the right track but I can't seem to get it t
work. I tried everything in my original spreadsheet and messed aroun
with it a bit but can't seem to get anything other than Zeros. Could b
just me as I've only ever messed with functions in Excel and not VBA.
made a dummy file with the pertinent information and hoping you migh
have some more insight for me?

Thanks again for the help

+-------------------------------------------------------------------
|Filename: Test Spreadsheet.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=682
+-------------------------------------------------------------------

 

[Ben McClave]

Hello,

After looking at your sheet, I think that you may be referencing the wrong CriteriaRange (F2:F2000 should be E2:E2000). This formula worked for me in cell G2:

=CountFromColumn(B2:B2000,E2:E2000,"Diagnostic Testing","Refurb Actuator")

Using your test sheet the formula returned 69. When I checked the result using a pivottable (to filter the list to just the parts meeting the two criteria), it also returned 69 unique part numbers.

Hope this helps,

Ben

 

[GS]

Ben McClave presented the following explanation :

Hello,

After looking at your sheet, I think that you may be referencing the wrong
CriteriaRange (F2:F2000 should be E2:E2000). This formula worked for me in
cell G2:

=CountFromColumn(B2:B2000,E2:E2000,"Diagnostic Testing","Refurb Actuator")

Using your test sheet the formula returned 69. When I checked the result
using a pivottable (to filter the list to just the parts meeting the two
criteria), it also returned 69 unique part numbers.

Hope this helps,

Ben

It might be a good idea to use a defined name for the range to ref
since inserting/deleting cols will cause the formulas to change the
ref. This might not be what's desired...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[kalkap]

;1607398']Ben McClave presented the following explanation :-

Hello,

After looking at your sheet, I think that you may be referencing th wrong
CriteriaRange (F2:F2000 should be E2:E2000). This formula worked fo me in
cell G2:

=CountFromColumn(B2:B2000,E2:E2000,"Diagnostic Testing","Refur Actuator")

Using your test sheet the formula returned 69. When I checked th result
using a pivottable (to filter the list to just the parts meeting th two
criteria), it also returned 69 unique part numbers.

Hope this helps,

Ben-

It might be a good idea to use a defined name for the range to ref
since inserting/deleting cols will cause the formulas to change the
ref. This might not be what's desired...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks for all the help. Seems to be working now on my tes
spreadsheet! Now to program it into my main sheet.

Thanks again

+-------------------------------------------------------------------
+-------------------------------------------------------------------