Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Total Occurrences in a Range (2000)

    Hi, I am wanting to count the number of times a particular string of data occurs within a range named chargedesc. I pulled the following formula from a Walkenbach book but get a Name? when I enter.

    Formula is: =(SUM(LEN(chargedesc))-SUM(LEN(SUBSTITUTE(chargedesc, text, ""))))/LEN(text)

    Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Occurrences in a Range (2000)

    You must either replace text with a quoted string representing the text string whose occurrences you want to count, or you must have put the text string in a cell named text.

    And the formula is an array formula, you must confirm it with Ctrl+Shift+Enter instead of just Enter.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total Occurrences in a Range (2000)

    Hans, thanks. Maybe I was not totally clear and this may change your reply. In Column F I have around 10000 string entries that are basically sentences such as "Research the internet for case material". However, there may be 900 other unique entries in column F. What I'd like to do is know how many times each one of these strings occurs. Will your modification do this?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Occurrences in a Range (2000)

    The formula from John Walkenbach's website counts how often a string occurs as part of the contents of one or more cells. For example, the string "do" occurs 3 times in these cells:

    <table border=1><td>The dodo is extinct</td><td>Yes, I do!</td></table>
    If I understand your description correctly, you are not looking for part of cell values. Do you have a list of unique entries, or do you want to know for EACH entry in column F how often it occurs? Or am I completely off?

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total Occurrences in a Range (2000)

    No. You are right on. I want to know how many times a the entire cell contents occur within a range. For example,

    f2: LONG DISTANCE TELEPHONE CHARGES: PHONE TO ABC123456
    f3: LONG DISTANCE TELEPHONE CHARGES: PHONE TO 205759xxxx
    through f10000

    I'd like to know how many times for example row 2 (exact match) was found in the range of f1-f10000.

    Make sense?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Occurrences in a Range (2000)

    In cell G1, enter the formula

    =COUNTIF($F$1:$F$10000,F1)

    and fill down to G10000 (you can use another column than G, of course)

  7. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total Occurrences in a Range (2000)

    Hans, thanks SO much. Works perfect

  8. #8
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total Occurrences in a Range (2000)

    Oops. Spoke too soon. Doesn't completely work. For example, using this forumula it appears that:
    cell f2 - The cow jumped over the moon
    cell f3 - The cow jumper over the moon and then over mars.

    Will equal each other. I wanted exact matches. For example, cell f2 - how many times did this exact verbiage - with nothing before or after occurs.

    Any suggestions?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Occurrences in a Range (2000)

    I don't understand that. COUNTIF only counts exact matches, not partial matches. If you had only F2 and F3, the COUNTIF formula would return 1 in both G2 and G3, since each value occurs only once. Please post a sample workbook if it doesn't work that way for you.

  10. #10
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total Occurrences in a Range (2000)

    Hans, sorry. You are right, as usual. I pasted the formula in the wrong cell (1 cell below where it should have been).

    My apologies and thanks.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Occurrences in a Range (2000)

    Never mind, could happen to anyone. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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