Counting the number of times a word appears in a worksheet
 

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.

Jig Bhakta wrote...
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.

If you want to count cells containing a particular substring, so if
your substring were "XYZ" and cell X99 contained "123XYZ456XYZ789" cell
XYZ would count as one match, use COUNTIF, e.g.,

=COUNTIF(Range,"*"&<YourSubstringHere&"*")

If you want to count every instance including multiple instances in the
same cell as separate matches, use

=SUMPRODUCT((LEN(Range)-LEN(SUBSTITUTE(Range,<YourSubstringHere,""))
/LEN(<YourSubstringHere))

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.

Missed a bracket

=SUMPRODUCT((LEN(Range)-LEN(SUBSTITUTE(Range,<YourSubstringHere,"")))
/LEN(<YourSubstringHere))


"Harlan Grove" wrote in message
oups.com...
Jig Bhakta wrote...
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.

If you want to count cells containing a particular substring, so if
your substring were "XYZ" and cell X99 contained "123XYZ456XYZ789" cell
XYZ would count as one match, use COUNTIF, e.g.,

=COUNTIF(Range,"*"&<YourSubstringHere&"*")

If you want to count every instance including multiple instances in the
same cell as separate matches, use

=SUMPRODUCT((LEN(Range)-LEN(SUBSTITUTE(Range,<YourSubstringHere,""))
/LEN(<YourSubstringHere))

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.