Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Quote:
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
|
|||
|
|||
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.
Last edited by kalkap : November 12th 12 at 09:35 PM |
#5
|
|||
|
|||
Quote:
Thanks again for the help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
|
|||
|
|||
Quote:
Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Countif with multiple criteria in the same column. | Excel Discussion (Misc queries) | |||
Multiple Row and Column Criteria | Excel Worksheet Functions | |||
How do I sum column D and F based on multiple column criteria? | Excel Worksheet Functions | |||
Multiple Criteria in same Column | Excel Worksheet Functions | |||
multiple criteria in one column | Excel Worksheet Functions |