=SUMPRODUCT(--(ISNUMBER(SEARCH("Bill",A1:A100))))

if you want it case sensitive, use Find not Search.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"steveo" wrote in message
oups.com...
Hi, does anyone know of a formula to count the occurances of text
within a range? the problem is there are more than one word in a cell
and I need to count the occurances of multiple words in the range.
Normally excel 2004(mac) will ignore the occurance of a word in a cell
if there is more than 1 word in a cell. Also how can I ignore the cells
that have a word i'm looking for if there are certain other words in
the cell that I do not wish to count? Yeah that would probably be
another formula.
any help would be appreciated. Thanks steveo