Dynamic Spilled Arrays - mutliple criteria to sum query

[tkmrxlsx]

I'm using the FILTER function to pull a subset of data (e.g. F1) from a Table. I then pull a Filter again to show certain characteristics of the data (e.g. F2) to assist analysis. And then I want to count how many times the F2 combination shows up in F1.

F1 contains all of the data fields, while F2 is just 3 data fields

But I don't know how to reference F2 results and use that to count what matches in F1. I used to do this with SUMPRODUCT (with old menu-bar Filter).


Here's the old calculation: SUMPRODUCT((G$27=$CD$100:$CD$20000)*(G31=$CE$100:$CE$20000)*(H31=$CF$100:$CF$20000)) The criteria (G$27,G$31,H$31) or F2 is now in the spill array G29# and the range is now D$100# or F1.


But how do I reference these individual criteria within a spilled array and perform a boolean argument?
Thanks!

 

[Fluff]

So with that data would you expect to get an answer of 1, namely row 101 is the only row that matches all 4 values in C49:F49?

 

[tkmrxlsx]

Yes, really just D49:F49.
F67 specifies "Head" for D100# result

 

[tkmrxlsx]

Another example:

dCDS_2015_InjuryDetail.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
36		Whole region
37			detail_ASPECT90	detail_SYSORG	detail_LESION
38	0	Thorax	Left	Skeletal	Fracture
39		Thorax	Left	Pulmonary - Lungs	Laceration
40		Thorax	Central	Arteries - Veins	Laceration
41		Thorax	Whole Region	Injured, Unknown	Injured, Unknown Lesion
42
43
44
45
46
47
48
49
50
51			# of records: 0
52
53			accid_a_MANCOLL	oaooo_a_SEATPOS	oiooo_a_REGION08	oiooo_a_AIS08	veooo_a_PDOF1	gvooo_a_DVTOTAL
54
55
56
57
58
59
60
61
62
63
64
65
66			Angle;Head-On	Front Left Side;Front Middle	Thorax	2;3;4;5;6	PDOF range	Delta-V range
67			Angle	Front Left Side	Thorax	2	>=0	>=0
68			Head-On	Front Middle		3	<=360	<=152
69						4
70						5
71						6
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99			PSU-a-CASENO	COI_a	accid_a_TIME	gvooo_a_CLIMATE	accid_a_MANCOLL	gvooo_a_SURCOND	gvooo_a_SPLIMIT	gvooo_a_VEHNO	gvooo_a_ANGTHIS	gvooo_a_ANGOTHER	gvooo_a_DVTOTAL	oaooo_a_DEATH	oiooo_a_OCCNO	oaooo_a_SEATPOS	oaooo_a_AGE	oaooo_a_SEX	oaooo_a_MANUSE	oiooo_a_INJNO	oiooo_a_REGION08	oiooo_a_ASPECT90	oiooo_a_SYSORG	oiooo_a_LESION	oiooo_a_AIS08	veooo_a_PDOF1
100			11-17	I-4	1151	Clear	Angle	Dry	89	1	270	350	59	2	1	Front Left Side	62	Male	Lap And Shoulder	4	Thorax	Bilateral	Skeletal	Fracture	3	280
101			11-17	I-5	1151	Clear	Angle	Dry	89	1	270	350	59	2	1	Front Left Side	62	Male	Lap And Shoulder	5	Thorax	Bilateral	Pulmonary - Lungs	Contusion	3	280
102			11-17	I-6	1151	Clear	Angle	Dry	89	1	270	350	59	2	1	Front Left Side	62	Male	Lap And Shoulder	6	Thorax	Bilateral	Pulmonary - Lungs	Laceration	4	280
103			11-17	I-7	1151	Clear	Angle	Dry	89	1	270	350	59	2	1	Front Left Side	62	Male	Lap And Shoulder	7	Thorax	Bilateral	Injured, Unknown	Injured, Unknown Lesion	3	280
104			11-17	I-8	1151	Clear	Angle	Dry	89	1	270	350	59	2	1	Front Left Side	62	Male	Lap And Shoulder	8	Thorax	Central			5	280
105			11-17	I-9	1151	Clear	Angle	Dry	89	1	270	350	59	2	1	Front Left Side	62	Male	Lap And Shoulder	9	Thorax	Central	Arteries - Veins	Laceration	4	280
106			11-17	I-12	1151	Clear	Angle	Dry	89	1	270	350	59	2	1	Front Left Side	62	Male	Lap And Shoulder	12	Thorax	Left	Respiratory	Rupture	4	280
107			11-3	I-2	53	Cloudy	Head-On	Wet	72	1	340	175	91	2	1	Front Left Side	21	Female-Not Preg	None Used/Avail	2	Thorax	Left	Skeletal	Fracture	3	0
108			11-3	I-3	53	Cloudy	Head-On	Wet	72	1	340	175	91	2	1	Front Left Side	21	Female-Not Preg	None Used/Avail	3	Thorax	Left	Pulmonary - Lungs	Laceration	4	0
109			11-3	I-4	53	Cloudy	Head-On	Wet	72	1	340	175	91	2	1	Front Left Side	21	Female-Not Preg	None Used/Avail	4	Thorax	Central	Arteries - Veins	Laceration	4	0
110			11-3	I-5	53	Cloudy	Head-On	Wet	72	1	340	175	91	2	1	Front Left Side	21	Female-Not Preg	None Used/Avail	5	Thorax	Whole Region	Injured, Unknown	Injured, Unknown Lesion	3	0
111			11-68	I-2	1329	Cloudy	Angle	Dry	80	1	10	250	21	0	1	Front Left Side	84	Female-Not Preg	Lap And Shoulder	2	Thorax	Right	Skeletal	Fracture	3	40
112			11-68	I-3	1329	Cloudy	Angle	Dry	80	1	10	250	21	0	1	Front Left Side	84	Female-Not Preg	Lap And Shoulder	3	Thorax	Central	Skeletal	Fracture	2	40
113			11-68	I-9	1329	Cloudy	Angle	Dry	80	1	10	250	21	0	1	Front Left Side	84	Female-Not Preg	Lap And Shoulder	9	Thorax	Bilateral	Pulmonary - Lungs	Contusion	3	40
HEAD

Cell Formulas
Range	Formula
F36	F36	=IF(G47="","",SUMPRODUCT((G$40=$CD$100:$CD$20000)*(G47=$CE$100:$CE$20000)*(H47=$CF$100:$CF$20000)))
B38	B38	=SUMPRODUCT(--(ISNUMBER(MATCH(C$38#>0,0))))
C38:F41	C38	=UNIQUE(FILTER(NTable[[oiooo_a_REGION08]:[oiooo_a_LESION]],(($C$36=oiooo_a_ASPECT90)*(INDIRECT($D$53)=$D$67)+(INDIRECT($D$53)=$D$68)+(INDIRECT($D$53)=$D$69)+(INDIRECT($D$53)=$D$70)+(INDIRECT($D$53)=$D$71)+(INDIRECT($D$53)=$D$72))*(INDIRECT($F$53)<>"")*((INDIRECT($F$53)=$F$67)+(INDIRECT($F$53)=$F$68)+(INDIRECT($F$53)=$F$69)+(INDIRECT($F$53)=$F$70))*((INDIRECT($E$53)=$E$67)+(INDIRECT($E$53)=$E$68)+(INDIRECT($E$53)=$E$69)+(INDIRECT($E$53)=$E$70))*((INDIRECT($G$53)=$G$67)+(INDIRECT($G$53)=$G$68)+(INDIRECT($G$53)=$G$69)+(INDIRECT($G$53)=$G$70)+(INDIRECT($G$53)=$G$71))))
G50	G50	=IFERROR(INDEX(INDIRECT(G$41),SMALL(IF(((detail_REGION08=$F$67)*(detail_ASPECT90=$G$40)),ROW(INDIRECT(G$41))-MIN(ROW(INDIRECT(G$41)))+1),ROWS($G$41:G49))),"")
D51	D51	=COUNTA(B100:B$20000)
D67:F67	D67	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (1-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
G67	G67	=IFERROR(VALUE(LEFT(AIS08_output,1)),"")
D68:F68	D68	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (2-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
G68	G68	=IFERROR(VALUE(MID(AIS08_output,3,1)),"")
D69:F69	D69	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (3-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
G69	G69	=IFERROR(VALUE(MID(AIS08_output,5,1)),"")
D70:F70	D70	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (4-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
G70	G70	=IFERROR(VALUE(MID(AIS08_output,7,1)),"")
D71:F71	D71	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (5-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
G71	G71	=IFERROR(VALUE(MID(AIS08_output,9,1)),"")
B72:D72	B72	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (6-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
B73:D73	B73	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (7-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
B74:D74	B74	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (8-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
B75:D75	B75	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (9-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
B76:D76	B76	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D$66),";",REPT(" ",LEN(TRIM(D$66)))), (10-1)*LEN(TRIM(D$66))+1, LEN(TRIM(D$66)))),"")
D100:AA113	D100	=FILTER(NTable,((INDIRECT(D53)=D67)+(INDIRECT(D53)=D68)+(INDIRECT(D53)=D69)+(INDIRECT(D53)=D70)+(INDIRECT(D53)=D71)+(INDIRECT(D53)=D72))*(INDIRECT(F53)<>"")*((INDIRECT(F53)=F67)+(INDIRECT(F53)=F68)+(INDIRECT(F53)=F69)+(INDIRECT(F53)=F70))*((INDIRECT(E53)=$E$67)+(INDIRECT(E53)=$E$68)+(INDIRECT(E53)=$E$69)+(INDIRECT(E53)=$E$70))*((INDIRECT(G53)=$G$67)+(INDIRECT(G53)=$G$68)+(INDIRECT(G53)=$G$69)+(INDIRECT(G53)=$G$70)+(INDIRECT(G53)=$G$71)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
AIS08_output	=HEAD!$G$66	G67:G71
MANCOLL_output	=HEAD!$D$66	D67:D71, B72:B76
REGION08_output	=HEAD!$F$66	D72:D76, F67:F71
SEATPOS_output	=HEAD!$E$66	C72:C76, E67:E71

 

[Fluff]

I think you will need to stick to your original formula, or use countifs

=COUNTIFS(W100:W1000,D49,X100:X1000,E49,Y100:Y1000,F49)