# Count Occurrences of Groups of text entries

1. ## 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.

Excel.png

2. ## 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. ## Re: Count Occurrences of Groups of text entries

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

....

edit

....

5. ## 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

6. ## 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. ## 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. ## 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. ## Re: Count Occurrences of Groups of text entries

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

Pete

10. ## 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

11. ## 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

EDIT Good morning

12. ## Re: Count Occurrences of Groups of text entries

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

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