Countif, Index, Match... not sure which to use

[Amy via OfficeKB.com]

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

 

[Amy via OfficeKB.com]

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.

 

[Biff]

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]

Thank you, thank you, thank you!!!

Have a great weekend Biff,
Amy

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

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thank you, thank you, thank you!!!

Have a great weekend Biff,
Amy

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