Counting a Table Contents

[Steve 1962]

Hi

I have a table that I want to summarise a count using certain criteria. That table is in cells F7 to J11.

The data that are being checked are in cells A1 to D24. I want the summary table noted above to check the date (column A), reference number column B), start & end (columns C & D) and only count if the locations found in the Start & End columns are not noted in the "Allowed Locations" which are identified in G1 to J4.

I have manually entered what the values should be in cells G8 to J11.

I have a feeling that it may be a MATCH / INDEX type solution but not sure. If there is an easier way, then all the better.

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Reference	Start	End		Reference	567	111	234	678
2	1/04/2020	567	Cairo	New York		Allowed Locations	Boston	Cairo	Boston	Auckland
3	1/04/2020	567	Boston	New York			New York	Auckland	New York	Las Vegas
4	1/04/2020	567	Boston	New York			Calgary	New York	Cairo	Los Angeles
5	1/04/2020	111	Dallas	Boston
6	1/04/2020	567	Boston	Calgary
7	2/04/2020	234	Boston	Cairo		Should Read -	567	111	234	678
8	2/04/2020	234	Los Angeles	Boston		1/04/2020	1	1	0	0
9	2/04/2020	567	Boston	Boston		2/04/2020	0	1	1	0
10	2/04/2020	567	Boston	New York		3/04/2020	0	2	0	3
11	2/04/2020	111	Boston	New York		4/04/2020	0	0	2	0
12	2/04/2020	567	Boston	New York
13	3/04/2020	111	New York	New York
14	3/04/2020	111	New York	Los Angeles
15	3/04/2020	111	Las Vegas	New York
16	3/04/2020	678	New York	Cairo
17	3/04/2020	678	New York	Cairo
18	3/04/2020	678	Seattle	Cairo
19	4/04/2020	111	New York	Cairo
20	4/04/2020	111	New York	Cairo
21	4/04/2020	567	New York	Boston
22	4/04/2020	234	Auckland	Boston
23	4/04/2020	234	Cairo	Boston
24	4/04/2020	234	Auckland	Boston
Sheet7

 

[Steve 1962]

OK - have found the issue. The reference column (B) was formatted as text. Now it is working fine as I've changed it to number.

Just want to say thanks very much to you two that had patience enough to get me to the end.

All the best and thanks.

 

[Amit Tandon]

You are welcome Steve