+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : COUNTIF to find occurences of duplicate strings within strings

  1. #1
    Registered User
    Join Date
    07-15-2009
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    23

    COUNTIF to find occurences of duplicate strings within strings

    Hi Guys,

    Really struggling with a query in excel.

    I am trying to use SUMIF to count the occurrences of a 3 character alphanumeric entry of text in a series of cells.

    I have a simple version which works however it counts the cells which have that occurrence in not the amount of times that string occurs.

    I know it would be better to rewrite the spreadsheet but that is not possible due to certain limitations.

    In this example the text in the field is

    "UH X4 - 314657 R01 22/02/09 - 314972 R09 03/05/09 - 315627 R01 09/06/09 - 315932 R09 25/06/09"

    This is telling me

    UH = Unacceptable history
    X4 = 4 Jobs have been done to this job
    xxxxxx = the job number
    XYY = The person working on it
    Date, the date the job was completed

    I have about 30 other rows similar to this within the column and I need to count all the times R01 and R02 occur in the whole column, not the ammount of cells that occurrence is in.

    This is what I am using to cound that column.

    =SUM(COUNTIF($N$6:$N$1000,"*R01*"))

    This calculates how many Cells R01 appears in however as you can see above R01 appears twice, I need it to count that as 2 not 1.

    Also if there is some way I can extract the dates from this field and calculate a mean average between the dates and flag up dates that are 3 weeks apart or less that would be great.

    Any help would be appreciated, I'm sorry for rambling on but I'm really stuck on this one.

    Thanks
    Ross
    Last edited by Deap; 07-15-2009 at 11:00 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: COUNTIF to find occurences

    Try:

    =SUMPRODUCT(--((LEN($N$6:$N$1000)-LEN(SUBSTITUTE($N$6:$N$1000,"R01","")))/3))

    the 3 represents the length of the text string "R01"
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    07-15-2009
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: COUNTIF to find occurences of duplicate strings within strings

    Brilliant that worked a treat, not just to change the R01 to all the other 200 engineer numbers we have.

    Now the hard part, extracting the dates from these cells and flagging up any cells that have dates of less than tree weeks apart.

    Any idea's I'm thinking this one may be impossible.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: COUNTIF to find occurences of duplicate strings within strings

    Since this new question is not related to the original.. please start a new thread.

    and mark this one as [Solved] please.

  5. #5
    Registered User
    Join Date
    07-15-2009
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: COUNTIF to find occurences of duplicate strings within strings

    Sorry, no worries. thanks again.

  6. #6
    Registered User
    Join Date
    07-15-2009
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: COUNTIF to find occurences of duplicate strings within strings

    Deleted post
    Last edited by Deap; 07-15-2009 at 11:01 AM. Reason: Unnecessary

+ 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