+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Jig Bhakta
    Guest

    [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. #2
    Harlan Grove
    Guest

    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. #3
    Peo Sjoblom
    Guest

    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. #4
    Bob Phillips
    Guest

    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. #5
    Jig Bhakta
    Guest

    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.

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1