ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Multiple criteria in same column (https://www.excelbanter.com/excel-programming/447588-multiple-criteria-same-column.html)

kalkap

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

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

Spencer101

Quote:

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

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

Perhaps an example workbook would be useful...

Ben McClave

Multiple criteria in same column
 
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

kalkap

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

kalkap

1 Attachment(s)
Quote:

Originally Posted by Ben McClave (Post 1607172)
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

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

Thanks again for the help.

Ben McClave

Multiple criteria in same column
 
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[_2_]

Multiple criteria in same column
 
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

Quote:

Originally Posted by GS[_2_] (Post 1607398)
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

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

Thanks again!


All times are GMT +1. The time now is 02:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com