# [SOLVED] Counting the number of times a word appears in a worksheet

1. ## [SOLVED] 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.

2. ## Re: Counting the number of times a word appears in a worksheet

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>))

3. ## Re: Counting the number of times a word appears in a worksheet

One way

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

Regards,

Peo Sjoblom

"Jig Bhakta" <[email protected]> wrote in message
news:[email protected]..
> 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.

4. ## Re: Counting the number of times a word appears in a worksheet

Missed a bracket

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

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]..
> 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>))
>

5. ## Re: Counting the number of times a word appears in a worksheet

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" <[email protected]> wrote in message
> news:[email protected]..
> > 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.

>
>
>