countif range then "* cell value *"

[TAJ Simmons]

I have this function....(excel 2002/XP)

=COUNTIF(Spreadsheetname!M2:M30,"*Plastic*")

It adds up the appearance of the word "Plastic" in a list.....so

Then if M2 has this value

Metal Plastic Wood

The answer would be 1


If M3 has this value
Wood Plastic

The answer would be 2

You get the idea.


What I want to achieve is to have value for the word 'Plastic' automatically put in from a list of things


So if I have a list

B2 plastic
B3 wood
B4 metal
B5 glass

My function (this is the best I can guess) would be (these are my attempts)
=COUNTIF(Spreadsheetname!M2:M30,B2)
=COUNTIF(Spreadsheetname!M2:M30,*B2*)
=COUNTIF(Spreadsheetname!M2:M30,"*Value=B2*")

Obviously (to you people) these do not work.

What's the answer.

Thanks
TAJ Simmons
microsoft powerpoint mvp

awesome - powerpoint backgrounds,
free powerpoint templates, tutorials, hints and tips etc
http://www.powerpointbackgrounds.com

 

[Frank Kabel]

Hi
nearly there. Try
=COUNTIF(Spreadsheetname!M2:M30,"*"&B2&"*")

-----Original Message-----
I have this function....(excel 2002/XP)

=COUNTIF(Spreadsheetname!M2:M30,"*Plastic*")

It adds up the appearance of the word "Plastic" in a list.....so

Then if M2 has this value

Metal Plastic Wood

The answer would be 1


If M3 has this value
Wood Plastic

The answer would be 2

You get the idea.


What I want to achieve is to have value for the

word 'Plastic' automatically put in from a list of things

 

[TAJ Simmons]

Frank,

Brilliant....it works a treat...I've would never of figured it out myself....it looks like some kind of cryptic perl
code!

Cheers
TAJ Simmons
microsoft powerpoint mvp