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

 

[Amit Tandon]

You may download the excel file from this link - Click here - wherein the above formula is illustrated.

 

[Steve 1962]

Thanks Amit. I will give it a go.

 

[Steve 1962]

HI Amit

Tried again and have downloaded your file. Not sure why it is not working as the formulas are correct.

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	0	0	0	0
9	2/04/2020	567	Boston	Boston		2/04/2020	0	0	0	0
10	2/04/2020	567	Boston	New York		3/04/2020	0	0	0	0
11	2/04/2020	111	Boston	New York		4/04/2020	0	0	0	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
Mr Excel

Cell Formulas
Range	Formula
G8:J11	G8	{=SUM(--(FREQUENCY(IF(ISNA(MATCH($C$2:$D$24,G$2:G$4,0))*ISNUMBER(MATCH($A$2:$A$24,$F8,0))*ISNUMBER(MATCH($B$2:$B$24,G$1,0)),ROW($A$2:$A$24)-ROW($A$2)+1,""),ROW($A$2:$A$24)-ROW($A$2)+1)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fluff]

Did you put the formula in G8, confirm it with CSE & then drag down & across?

 

[Steve 1962]

Yes, used CSE to make sure it was an array formula.

 

[Fluff]

But did you just put the formula in G8, or did you have G8:J11 selected when you entered the formula?
All your cells are looking at F8, which is wrong.

 

[Steve 1962]

Tried again. I think I have copied it correctly.

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	0	0	0	0
9	2/04/2020	567	Boston	Boston		2/04/2020	0	0	0	0
10	2/04/2020	567	Boston	New York		3/04/2020	0	0	0	0
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
Mr Excel

Cell Formulas
Range	Formula
G8:J11	G8	{=SUM(--(FREQUENCY(IF(ISNA(MATCH($C$2:$D$24,G$2:G$4,0))*ISNUMBER(MATCH($A$2:$A$24,$F8,0))*ISNUMBER(MATCH($B$2:$B$24,G$1,0)),ROW($A$2:$A$24)-ROW($A$2)+1,""),ROW($A$2:$A$24)-ROW($A$2)+1)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fluff]

It works for me, check that you dates are real dates & not text.

 

[Steve 1962]

Yes, checked that the dates are real dates and they are.

I'll go through all the entries again as I don't know why I'm getting a different answer. Something is not right on my end but I don't know what it could be.

 

[Amit Tandon]

Does the formula not show correct results when you download the excel file? Please download a fresh copy and check.