Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Countif, Index, Match... not sure which to use
I need to be able to count how times the word 'CNC' occurs in column B, only
when the value in column A equals 'Assembly'. The text in column A is exact, but the text in column B needs to search for 'CNC' using wildcards, because there are many words in each cell in column B; and sometimes there is nothing in column B. I'm not sure which function to use or combination of functions to use for this. Here is what some of the data looks like: Column A Column B Assembly Assembly Communication Assembly Ergonomics; Safety Ergonomics Assembly More safety related training; Safety Ergonomics Assembly Assembly Better ergonomics; Safety Ergonomics Assembly Ergonomics; Safety Ergonomics; computer Assembly None Assembly None Assembly Computer Functioning; Computer Systems; Communication Assembly Assembly Service school for the repair group Assembly Assembly Assembly Assembly Null More people skill; giving & receiving feedback; presenting goals ideas, etc. Null AS400; Computer Systems; safety Other Business Process, Manufacturing Machining CNC Machining Machining Basic machining; I need to at least be able to talk the language; CNC/Machining/Programming GPMG Gear Training Reman Salaried Assembly Computer training to properly perform point duties; Computer Systems Other People Skills Other Working with Al Salentine; computer Machining Quality Assembly Quality -- Thank you, Amy@H-D |
#2
|
|||
|
|||
I've tried this:
=SUMPRODUCT((B2:B412="Assembly")*(C2:C412="cnc")) But it doesn't work if there are words before 'cnc' in any of the cells. I'm not sure how to use the wildcards for this to work. Amy wrote: I need to be able to count how times the word 'CNC' occurs in column B, only when the value in column A equals 'Assembly'. The text in column A is exact, but the text in column B needs to search for 'CNC' using wildcards, because there are many words in each cell in column B; and sometimes there is nothing in column B. I'm not sure which function to use or combination of functions to use for this. Here is what some of the data looks like: Column A Column B Assembly Assembly Communication Assembly Ergonomics; Safety Ergonomics Assembly More safety related training; Safety Ergonomics Assembly Assembly Better ergonomics; Safety Ergonomics Assembly Ergonomics; Safety Ergonomics; computer Assembly None Assembly None Assembly Computer Functioning; Computer Systems; Communication Assembly Assembly Service school for the repair group Assembly Assembly Assembly Assembly Null More people skill; giving & receiving feedback; presenting goals ideas, etc. Null AS400; Computer Systems; safety Other Business Process, Manufacturing Machining CNC Machining Machining Basic machining; I need to at least be able to talk the language; CNC/Machining/Programming GPMG Gear Training Reman Salaried Assembly Computer training to properly perform point duties; Computer Systems Other People Skills Other Working with Al Salentine; computer Machining Quality Assembly Quality -- Thank you, Amy@H-D |
#3
|
|||
|
|||
Hi!
Try this: Use 2 cells to hold the criteria: C1 = Assembly D1 = CNC =SUMPRODUCT(--(A1:A100=C1),--(ISNUMBER(SEARCH(D1,B1:B100)))) Biff "Amy via OfficeKB.com" wrote in message ... I need to be able to count how times the word 'CNC' occurs in column B, only when the value in column A equals 'Assembly'. The text in column A is exact, but the text in column B needs to search for 'CNC' using wildcards, because there are many words in each cell in column B; and sometimes there is nothing in column B. I'm not sure which function to use or combination of functions to use for this. Here is what some of the data looks like: Column A Column B Assembly Assembly Communication Assembly Ergonomics; Safety Ergonomics Assembly More safety related training; Safety Ergonomics Assembly Assembly Better ergonomics; Safety Ergonomics Assembly Ergonomics; Safety Ergonomics; computer Assembly None Assembly None Assembly Computer Functioning; Computer Systems; Communication Assembly Assembly Service school for the repair group Assembly Assembly Assembly Assembly Null More people skill; giving & receiving feedback; presenting goals ideas, etc. Null AS400; Computer Systems; safety Other Business Process, Manufacturing Machining CNC Machining Machining Basic machining; I need to at least be able to talk the language; CNC/Machining/Programming GPMG Gear Training Reman Salaried Assembly Computer training to properly perform point duties; Computer Systems Other People Skills Other Working with Al Salentine; computer Machining Quality Assembly Quality -- Thank you, Amy@H-D |
#4
|
|||
|
|||
Thank you, thank you, thank you!!!
Have a great weekend Biff, Amy Biff wrote: Hi! Try this: Use 2 cells to hold the criteria: C1 = Assembly D1 = CNC =SUMPRODUCT(--(A1:A100=C1),--(ISNUMBER(SEARCH(D1,B1:B100)))) Biff I need to be able to count how times the word 'CNC' occurs in column B, only [quoted text clipped - 46 lines] Machining Quality Assembly Quality -- Thank you, Amy@H-D |
#5
|
|||
|
|||
You're welcome. Thanks for the feedback!
Biff "Amy via OfficeKB.com" wrote in message ... Thank you, thank you, thank you!!! Have a great weekend Biff, Amy Biff wrote: Hi! Try this: Use 2 cells to hold the criteria: C1 = Assembly D1 = CNC =SUMPRODUCT(--(A1:A100=C1),--(ISNUMBER(SEARCH(D1,B1:B100)))) Biff I need to be able to count how times the word 'CNC' occurs in column B, only [quoted text clipped - 46 lines] Machining Quality Assembly Quality -- Thank you, Amy@H-D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |