+ Reply to Thread
Results 1 to 8 of 8

Formula to Count the Number of Occurrences of a Text String in a Range

  1. #1
    Registered User
    Join Date
    09-18-2009
    Location
    Montreal
    MS-Off Ver
    Excel 2008 MAC
    Posts
    6

    Formula to Count the Number of Occurrences of a Text String in a Range

    Hi guys!

    I am using this formula:

    =SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")

    What I need to do is to be able to target a cell and use the content of that cell in my "text" section, instead of actually typing the text.

    Exemple:

    =SUM(LEN(A5:A142)-LEN(SUBSTITUTE(A5:A142,"Shot by Guillaume Poulin","")))/LEN("Shot by Guillaume Poulin")


    Let's assume I have:

    A143 ; Guillaume Poulin
    A144 ; Vincent Lecavalier
    A145 ; Patrick Kane


    My goal is that in my formula I can say "Shot by A143" instead of "Shot by Guillaume Poulin", so that I can refer to cells instead of actual text. That would prevent me from having to manually change the words.

    That for the help!

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Formula to Count the Number of Occurrences of a Text String in a Range

    If the text string occurs only once per cell (if at all) I would suggest you revert to COUNTIF, eg:

    Please Login or Register  to view this content.
    If a string may appear multiple times per cell then I would use SUMPRODUCT over CSE array, eg:

    Please Login or Register  to view this content.
    But to reiterate the former if viable is certainly preferable
    Last edited by DonkeyOte; 09-18-2009 at 04:20 PM. Reason: failed to update refs in SUMPRODUCT, doh!

  3. #3
    Registered User
    Join Date
    09-18-2009
    Location
    Montreal
    MS-Off Ver
    Excel 2008 MAC
    Posts
    6

    Re: Formula to Count the Number of Occurrences of a Text String in a Range

    The string does appear more then once. using sumproduct won't prevent me from having to manually change Guillaume Poulin to let say Vincent Lecavalier, instead of being able to just copie the formula to B:144 and having A144 automatically inserted.

    sorry btw, i'm french so my english is sad...

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Formula to Count the Number of Occurrences of a Text String in a Range

    poug1903, apologies but I just recently updated the SUMPRODUCT example given I had initially failed to update so as to make dynamic (more fool me)... suggest taking another look.

    In regard to a string appearing multiple times, just to be clear, I meant that "shot by xyz" could appear multiple times in any one given cell in your range - ie that string may appear twice in A10 ... if that is indeed the case then yes you should (IMO) adopt the SUMPRODUCT based approach.

  5. #5
    Registered User
    Join Date
    09-18-2009
    Location
    Montreal
    MS-Off Ver
    Excel 2008 MAC
    Posts
    6

    Re: Formula to Count the Number of Occurrences of a Text String in a Range

    based on your reply I used this formula:
    B148: =SUM(LEN(A5:A142)-LEN(SUBSTITUTE(A5:A142,"Shot by "&A148&"","")))/LEN("Shot by "&A148&"")

    it works very well. the problem I encounter now is that if I copie paste it to B149

    I still get:
    A148 and not A149 in my formulas. Thanks for the update on sumproduct I'm trying this right away...

  6. #6
    Registered User
    Join Date
    09-18-2009
    Location
    Montreal
    MS-Off Ver
    Excel 2008 MAC
    Posts
    6

    Re: Formula to Count the Number of Occurrences of a Text String in a Range

    I'm using your SUMPRODUCT formula and it works wonder, tho still the same problem. When I copie-paste, it doesn't chage my a143 to a144 in the formulas

  7. #7
    Registered User
    Join Date
    09-18-2009
    Location
    Montreal
    MS-Off Ver
    Excel 2008 MAC
    Posts
    6

    Re: Formula to Count the Number of Occurrences of a Text String in a Range

    woot just works now!

    THANKS SOO MUCH!!

  8. #8
    Registered User
    Join Date
    11-02-2011
    Location
    Chennai
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Formula to Count the Number of Occurrences of a Text String in a Range

    i need the above formula with example....

    kindly advice

+ 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