Formula to count how many cells contains a name & within that month

[Jamesraywebber85]

I have a chart with peoples name & across the top the months. On another sheet I have a list of files that follow this name format: Name_mm-dd-yyyy_Case #000000.PDF.

Is there a formula that I can look through that list of files & count how many each person has and place in the correct column based on which month it is for? I have added screenshots to help visualize what I would like. The 1st image is the list of Files & the 2nd image is the results which I need to show. I have also attached an example of my workbook.

List of Files.JPGResults.JPG

Thank you for your help in advance!

[Pete_UK]

You can use this formula in cell P2 of Sheet1:

=COUNTIFS(Sheet2!$A:$A,$O2&"*",Sheet2!$A:$A,"*"&MONTH(P$1)&"*"&YEAR(P$1)&"*")

then copy across and down as required.

Hope this helps.

Pete

[jeffreybrown]

In B3 >> =LEFT(A3,FIND("_",A3)-1)
In C3 >> =LEFT(MID(A3,FIND("_",A3)+1,LEN(A3)),FIND("_",MID(A3,FIND("_",A3)+1,LEN(A3)))-1)+0
In D4 >> =MID(A3,FIND("Case",A3),LEN(A3))

Now on your table in P2 >> =SUMPRODUCT(--(MONTH(P$1)=MONTH(Sheet2!$C$3:$C$19)),--(Sheet1!$O2=Sheet2!$B$3:$B$19))

[Pete_UK]

Sorry, slight amendment needed, as follows:

=COUNTIFS(Sheet2!$A:$A,$O2&"*",Sheet2!$A:$A,"*"&MONTH(P$1)&"????"&YEAR(P$1)&"*")

Hope this helps.

Pete

[jeffreybrown]

I'm guessing Pete's method is much neater.

[CARACALLA]

Sheet2
copy and paste the strings in A3:A19 as values

In the ribbon click on data and click on Text to columns

Select Delimited

Next

Select other and in the box put _ as a delimiter

Next

Destination A3

Finish

Sheet1

P2=SUMPRODUCT((Sheet2!$A$3:$A$19=Sheet1!$O2)*(MONTH(Sheet2!$B$3:$B$19)=MONTH(P$1)))