I need to count the number of dates that show up in a month in a range, based on the associated name

[Hammer21]

Hi everyone, its been ages since ive been able to post, but it feels good to be back. I hope everyone is well.

So, I have a small request that for some reason I just cant seem to figure out. I need to have excel do a lookup and countif, or some version of those. I have pasted a view of what I am trying to accomplish below, though of course I just did a manual plug and I want to automate with some type of a formula what is shown in the orange cells. Basically I have a list of names and various dates over the course of three months. I need to find a way to lookup the name and then count a date if it falls within a given month. If it lands on any day in that month I want it to be counted. Any help that anyone can provide would be greatly appreciated.

Thanks!

	A	B	C	D	E	F	G
1		Sep-18	Nov-18	Dec-18		Smith	9/14/2018
2	Smith	2	0	1		Smith	9/22/2018
3	Jones	1	3	1		Smith	12/5/2018
4						Smith	12/12/2017
5						Smith	12/12/2017
6						Smith	12/12/2017
7						Smith	12/12/2017
8						Smith	12/12/2017
9						Jones	11/2/2018
10						Jones	11/1/2018
11						Jones	12/11/2018
12						Jones	9/11/2018
13						Jones	11/20/2018

<tbody>
</tbody>

 

[Aladin Akyurek]

Assuming that Sep-18 is the display form of 2018-09-01, i.e. a first day date...

In B2 enter, copy across to D2, and down:

=SUMPRODUCT(--($F$1:$F$13=$A2),--($G$2:$G$13-DAY($G$2:$G$13)+1=B$1))

 

[Hammer21]

Thanks so much, Aladin

 

[JoeMo]

Here's an alternative.

Excel Workbook
	A	B	C	D	E	F	G
1		18-Sep	18-Nov	18-Dec		Smith	9/14/2018
2	Smith	2	0	1		Smith	9/22/2018
3	Jones	1	3	1		Smith	12/5/2018
4						Smith	12/12/2017
5						Smith	12/12/2017
6						Smith	12/12/2017
7						Smith	12/12/2017
8						Smith	12/12/2017
9						Jones	11/2/2018
10						Jones	11/1/2018
11						Jones	12/11/2018
12						Jones	9/11/2018
13						Jones	11/20/2018
Sheet4

 

[Aladin Akyurek]

Thanks so much, Aladin

You are welcome.

 

[markmzz]

Hi!

Another small modification in the suggestion until now:

=SUMPRODUCT(--($F$1:$F$13=$A2),--(--TEXT($G$1:$G$13,"m/yyyy")=B$1))

Markmzz