countif range then "* cell value *"

  • Thread starter Thread starter TAJ Simmons
  • Start date Start date
T

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
 
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
Click to expand...
word 'Plastic' automatically put in from a list of things
 
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
 
Back
Top