CountIf Different Tab

[kparadise]

Hello,

I have a workbook with two tabs. [Tab1] contains a list of 50 ID numbers [Column A] and then a start date [Column B] and an end date [Column C].

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]START DATE
[/TD]
[TD]END DATE
[/TD]
[TD]# of Reports
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ABCD
[/TD]
[TD]10/4/2017
[/TD]
[TD]3/31/2018
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]ZYX
[/TD]
[TD]10/24/2017
[/TD]
[TD]3/31/2018
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]ABBB
[/TD]
[TD]10/28/2017
[/TD]
[TD]1/5/2018
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]ZYYYY
[/TD]
[TD]12/1/2017
[/TD]
[TD]12/12/2017
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

[Tab2] contains each instance [Column B] where the ID number [Column A] appeared on a report.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]Occurrence
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ABCD
[/TD]
[TD]10/1/2017
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]ABCD
[/TD]
[TD]11/1/2017
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]ABCD
[/TD]
[TD]12/1/2017
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]ABCD
[/TD]
[TD]1/15/2018
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]ABCD
[/TD]
[TD]1/31/2018
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]ZYX
[/TD]
[TD]10/25/2017
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]ZYYYY
[/TD]
[TD]12/1/2017
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]ZYYYY
[/TD]
[TD]12/12/2017
[/TD]
[/TR]
</tbody>[/TABLE]

What I am trying to accomplish is in [Column D] of [Tab1]. I want to count the number of times from [Tab2] the ID appeared on this specific report. I need the date of the occurance on the report to be greater than or equal to the START DATE AND less than or equal to the END DATE.

 

[Joe4]

You can use COUNTIFS for this, which allows for multiple conditions.
So your formula in cell D2 on Tab1 would look like this:

=COUNTIFS(Tab2!A:A,Tab1!A2,Tab2!B:B,">=" & Tab1!B2,Tab2!B:B,"<=" & Tab1!C2)

and copy down.

 

[kparadise]

Perfect!