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

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

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" <[email protected]> wrote in message
news:[email protected]...
>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

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

You're welcome. Thanks for the feedback!

Biff

"Amy via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> 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