+ Reply to Thread
Results 1 to 12 of 12

Count Occurrences of Groups of text entries

  1. #1
    Registered User
    Join Date
    06-24-2011
    Location
    North East England
    MS-Off Ver
    Office 365
    Posts
    41

    Count Occurrences of Groups of text entries

    Morning everyone, hoping you can help.

    I have the below example of data organized by entries of "U", "P" or "S" against a name and under a given date.

    I need three separate formulas (for "U", "P" and "S") to count the occurrences of grouped letters arranged under consecutive dates, but counting only after the date entered. The desired outcomes are shown in the orange box in the below example.

    I hope my need is clear, please just ask if not.


    Excel.png
    Attached Files Attached Files
    Last edited by liam_bettinson; 04-03-2019 at 05:23 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,899

    Re: Count Occurrences of Groups of text entries

    Your picture does not show the row or column references that you are using, so rather than guess, it would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-24-2011
    Location
    North East England
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Count Occurrences of Groups of text entries

    Hi Pete, thanks for the advice. I have attached a file as suggested, hope this helps.

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Count Occurrences of Groups of text entries

    ....

    edit

    you've described clearly your need

    ....
    Last edited by canapone; 04-03-2019 at 05:51 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Count Occurrences of Groups of text entries

    Hi

    a first idea

    In G12 to be copied across

    =SUMPRODUCT(($B3:$P3&$C3:$Q3&LEFT($D3:$R3&"@")=REPT(G$11,2)&"@")*($B$2:$P$2>$B$8))

    For the moment I consider names in the same order of database in A2:P5.

    Name 1 and group of P after Jan 4: formula returns only one group/one serie.

    I hope it could be clear where formula is going...

    Regards
    Attached Files Attached Files
    Last edited by canapone; 04-03-2019 at 06:18 AM.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,899

    Re: Count Occurrences of Groups of text entries

    You can put this formula in B12:

    =COUNTIFS(INDEX($B$3:$P$5,MATCH($A12,$A$3:$A$5,0),0),B$11,$B$2:$P$2,">"&$B$8)

    Copy across into C12:D12, then copy the 3 formulae down as required.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    06-24-2011
    Location
    North East England
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Count Occurrences of Groups of text entries

    Hi Canapone, many thanks for the formula. I have transposed this across to P & S and it is not picking up the single entry 'P' for Name 1. Only appears to pick up groups of 2 or more?

  8. #8
    Registered User
    Join Date
    06-24-2011
    Location
    North East England
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Count Occurrences of Groups of text entries

    Hi Pete_UK, thanks for the formula, but this appears to only count the individual occurrences of each letter, rather than counting group occurrences of each letter?

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,899

    Re: Count Occurrences of Groups of text entries

    Sorry, I thought you wanted the first table (COUNT).

    Pete

  10. #10
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Count Occurrences of Groups of text entries

    Hi, (ciao Pete!)

    single entries

    =sumproduct(($B3:$P3&left($C3:$Q3&"@")=G$11&"@")*($B$2:$P$2>$B$8))


    Edit: for Your convenience, if you'd need to scramble the order of the names

    =SUMPRODUCT((INDEX($B$3:$P$5,MATCH($F12,$A$3:$A$5,0),)&LEFT(INDEX($C$3:$Q$5,MATCH($F12,$A$3:$A$5,0),)&"@")=G$11&"@")*($B$2:$P$2>$B$8))


    Regards
    Attached Files Attached Files
    Last edited by canapone; 04-03-2019 at 06:31 AM.

  11. #11
    Registered User
    Join Date
    11-22-2018
    Location
    Roma,Italia
    MS-Off Ver
    2013
    Posts
    40

    Re: Count Occurrences of Groups of text entries

    Good morning
    in G12 to activate with CTRL + SHIFT + ENTER and drag to the right and then down

    =SUM(--(FREQUENCY(IF(INDIRECT(ADDRESS(MATCH($F12,$A$3:$A$5,0)+2,MATCH($B$8,$B$2:$P$2,0)+2)&":"&ADDRESS(MATCH($F12,$A$3:$A$5,0)+2,16))=G$11,COLUMN(INDIRECT(ADDRESS(MATCH($F12,$A$3:$A$5,0)+2,MATCH($B$8,$B$2:$P$2,0)+2)&":"&ADDRESS(MATCH($F12,$A$3:$A$5,0)+2,16)))),IF(INDIRECT(ADDRESS(MATCH($F12,$A$3:$A$5,0)+2,MATCH($B$8,$B$2:$P$2,0)+2)&":"&ADDRESS(MATCH($F12,$A$3:$A$5,0)+2,16))<>G$11,COLUMN(INDIRECT(ADDRESS(MATCH($F12,$A$3:$A$5,0)+2,MATCH($B$8,$B$2:$P$2,0)+2)&":"&ADDRESS(MATCH($F12,$A$3:$A$5,0)+2,16)))))>0))

    EDIT Good morning
    Stefano reads us everywhere D@nilo
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-24-2011
    Location
    North East England
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Count Occurrences of Groups of text entries

    Canapone, your formula for single entries works perfectly, many, many thanks for your assistance!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to count occurrences of text
    By knuckledownlacrosse in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2015, 01:16 PM
  2. [SOLVED] Count occurrences of text between blanks
    By Tannenbaum1 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-16-2014, 01:17 PM
  3. count the occurrences of text
    By Elainefish in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-01-2013, 09:32 AM
  4. Replies: 3
    Last Post: 01-09-2013, 07:55 PM
  5. Count occurrences of text with various criteria
    By ludgirardi in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-20-2012, 05:31 AM
  6. Count occurrences of text with various criteria
    By ludgirardi in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 12-18-2012, 07:50 PM
  7. Count the occurrences of a text string?
    By New2MSExcel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-24-2010, 08:47 PM

Tags for this Thread

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