The formula given by Peo worked....

Thanks.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT((LEN(A1:F30)-(LEN(SUBSTITUTE(A1:F30,"word",""))))/LEN("word"))


Regards,

Peo Sjoblom

"Jig Bhakta" wrote in message
...
Hi,

I want to have a formula in a cell that counts the number of time a
certain
word appears in any cell in a worksheet. i've tried using count but it
only
counts for numbers, not text.

Thanks,

Jignesh.