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!

[TABLE="width: 496"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD="align: center"]Sep-18
[/TD]
[TD="align: center"]Nov-18
[/TD]
[TD="align: center"]Dec-18
[/TD]
[TD][/TD]
[TD]Smith
[/TD]
[TD="align: right"]9/14/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: center"]Smith
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]1
[/TD]
[TD][/TD]
[TD]Smith
[/TD]
[TD="align: right"]9/22/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: center"]Jones
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[TD][/TD]
[TD]Smith
[/TD]
[TD="align: right"]12/5/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith
[/TD]
[TD="align: right"]12/12/2017
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith
[/TD]
[TD="align: right"]12/12/2017
[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith
[/TD]
[TD="align: right"]12/12/2017
[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith
[/TD]
[TD="align: right"]12/12/2017
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith
[/TD]
[TD="align: right"]12/12/2017
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones
[/TD]
[TD="align: right"]11/2/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones
[/TD]
[TD="align: right"]11/1/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones
[/TD]
[TD="align: right"]12/11/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones
[/TD]
[TD="align: right"]9/11/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones
[/TD]
[TD="align: right"]11/20/2018
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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