Counting Table

[Steve 1962]

Hi

I did repost this post but I'm not sure that it came through, so just posting again just in case it didn't work the first time. Apologies if this is a double-up.

I have added an additional column C (Exception) which has either a 1 or 2 or can be some other number.

I would like the current counting table that we now have working fine to now only count the entries if they are anything but 1 in the "Exception" column.
Eg: Count as normal but do not count those cells which have a 1 in them.

Thanks

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

Cell Formulas
Range	Formula
H8:K11	H8	{=SUM(--(FREQUENCY(IF(ISNA(MATCH($D$2:$E$24,H$2:H$4,0))*ISNUMBER(MATCH($A$2:$A$24,$G8,0))*ISNUMBER(MATCH($B$2:$B$24,H$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.

 

[jasonb75]

I think that this is what you're asking for, in H8, remember to confirm the array with Ctrl Shift Enter

=SUM(--(FREQUENCY(IF(ISNA(MATCH($D$2:$E$24,H$2:H$4,0))*ISNUMBER(MATCH($A$2:$A$24,$G8,0))*ISNUMBER(MATCH($B$2:$B$24,H$1,0))*($C$2:$C$24<>1),ROW($A$2:$A$24)-ROW($A$2)+1,""),ROW($A$2:$A$24)-ROW($A$2)+1)>0))

 

[Amit Tandon]

In continuation of the previous / separate post by you, you may use below Array Formula (Ctrl+Shift+Enter) - this adds another condition of "($C$2:$C$24<>1)" to my earlier formula, to exclude column C cells with 1, and as already stated by jasonb:

=SUM(--(FREQUENCY(IF(ISNA(MATCH($D$2:$E$24,H$2:H$4,0))*ISNUMBER(MATCH($A$2:$A$24,$G8,0))*ISNUMBER(MATCH($B$2:$B$24,H$1,0))*($C$2:$C$24<>1),ROW($A$2:$A$24)-ROW($A$2)+1,""),ROW($A$2:$A$24)-ROW($A$2)+1)>0))

 

[Steve 1962]

Hey thanks very much Amit & Jasonb.

Works fine. You guys are smart.

All the best.

 

[jasonb75]

I thought that the formula looked a bit clumsy for the task it performs, it took me a while to figure out the logic in order to clean it up a bit.

Same results, no need to array confirm.

=SUMPRODUCT(($A$2:$A$24=$G8)*($B$2:$B$24=H$1)*($C$2:$C$24<>1)*((ISNA(MATCH($D$2:$D$24,H$2:H$4,0))+ISNA(MATCH($E$2:$E$24,H$2:H$4,0)))>0))