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
Last edited by liam_bettinson; 04-03-2019 at 05:23 AM.
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
Hi Pete, thanks for the advice. I have attached a file as suggested, hope this helps.
....
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.
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
Last edited by canapone; 04-03-2019 at 06:18 AM.
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
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?
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?
Sorry, I thought you wanted the first table (COUNT).
Pete
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
Last edited by canapone; 04-03-2019 at 06:31 AM.
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
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)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks