Counting the number of occurrences in a calendar year based on location

[leveyc]

Hi,

I have a few thousand rows of historical data, based on when a location was setup and operational (date exists in a column in the data), I need to count how many times in any given year of the past ten years the location was visited (date exists in a column in the data) and then by site, hope this makes sense and thanks in advance for the help

Data

Data Format				Results
Location	Opened	Visited		Opened	2015	2016	2017	2018	2019
Site 1	2/3/18	4/1/18		2015			1	1
Site 1	2/3/18	6/7/18		2016
Site 1	2/3/18	4/1/19		2017
Site 1	2/3/18	7/24/20		2018				2	1
Site 2	6/7/15	9/16/17
Site 2	6/7/15	11/6/18
				Sites	2015	2016	2017	2018	2019
				Site 1				2	1
				Site 2			1	1
				Site 3

 

[JamesCanale]

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Data Format				Results
2	Location	Opened	Visited		Opened	2015	2016	2017	2018	2019
3	Site 1	2/3/2018	4/1/2018		2015			1	1
4	Site 1	2/3/2018	6/7/2018		2016
5	Site 1	2/3/2018	4/1/2019		2017
6	Site 1	2/3/2018	7/24/2020		2018				2	1
7	Site 2	6/7/2015	9/16/2017
8	Site 2	6/7/2015	11/6/2018
9					Sites	2015	2016	2017	2018	2019
10					Site 1				2	1
11					Site 2			1	1
12					Site 3
Sheet30

Cell Formulas
Range	Formula
F3:J6	F3	=SUMPRODUCT(--(YEAR($B$3:$B$8)=$E3),--(YEAR($C$3:$C$8)=F$2))
F10:J12	F10	=SUMPRODUCT(--($A$3:$A$8=$E10),--(YEAR($C$3:$C$8)=F$2))