Try this:

For misc text in A1:A1000 and test text in B1

C1:
=SUMPRODUCT(LEN(A1:A1000)-LEN(SUBSTITUTE(UPPER(A1:A1000),UPPER(B1),"")))/LEN(B1)

That formula counts the occurrences of the B1 value in the range
A1:A1000

Note: the UPPER function makes the formua NOT case sensitive.

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=512494