Is there away to "Ingnore Blank Cells"?

"Bob Phillips" wrote:

The sumproduct variants will not work on a entire column, it must be a
specified range, but you can make them large.

=SUMPRODUCT(--(B1:B300<""),COUNTIF(A1,"*"&B1:B300&"*"))0

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Prohock" wrote in message
...
Thanks to everyone for their assistance, it works perfect! One more
question,
How would you adapt the formula so that it test any value that is located
in
Column B. Currently if I try to test the entire column I get false results
because of blank cells? Ie

"Aladin Akyurek" wrote:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$1 :$B$3,A1)))+0

A result of 1 means a hit, 0 no hit.

Prohock wrote:
I need a function that will use a column of text values and test these
values
to see if one or more of the values exist in a single cell. If it does
I need
the function to return true or false.

Ie. cell A1 contains the text "Jim Smith" the B column contains the
test
names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is
"jim".
Because Jim is in the cell A1 I would need the function in C1 to
return the
value "true". If A1 contained the text "bob smith" then function in C1
would
return the value "false".