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" 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

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" 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