Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 4
Default 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   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by kalkap View Post
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...
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 173
Default 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   Report Post  
Junior Member
 
Posts: 4
Default

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 10:35 PM
  #5   Report Post  
Junior Member
 
Posts: 4
Default

Quote:
Originally Posted by Ben McClave View Post
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.
Attached Files
File Type: zip Test Spreadsheet.zip (51.8 KB, 60 views)


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 173
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Junior Member
 
Posts: 4
Default

Quote:
Originally Posted by GS[_2_] View Post
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!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Countif with multiple criteria in the same column. Harley Excel Discussion (Misc queries) 5 May 5th 23 07:44 PM
Multiple Row and Column Criteria wade04 Excel Worksheet Functions 8 February 13th 09 12:55 AM
How do I sum column D and F based on multiple column criteria? sharon t Excel Worksheet Functions 12 August 20th 07 03:44 PM
Multiple Criteria in same Column Charlene Excel Worksheet Functions 9 February 19th 07 07:04 PM
multiple criteria in one column Barb Excel Worksheet Functions 1 December 3rd 04 08:49 PM


All times are GMT +1. The time now is 04:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"