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]

Hi & welcome to MrExcel.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a “Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[tkmrxlsx]

Added spreadsheet capture:

dCDS_2015_InjuryDetail.xlsm
	D	E	F	G	H	I	J	K
39
40				Left
41				detail_SYSORG	detail_LESION
42			SUMPRODUCT(--(what to enter here? G42#	Head	Left	Brain	Injured, Unknown Lesion
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	Front Left Side;Front Middle	Head	2;3;4;5;6	PDOF range	Delta-V range
67	Angle	Front Left Side	Head	2	>=0	>=0
68		Front Middle		3	<=45	<=50
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
100	11-17	I-1	1151	Clear	Angle	Dry	89	1
HEAD

Cell Formulas
Range	Formula
G42:J42	G42	=UNIQUE(FILTER(NTable[[oiooo_a_REGION08]:[oiooo_a_LESION]],(($G$40=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))))
D51	D51	=COUNTA(B100:B$20000)
D67:F67	E67	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E$66),";",REPT(" ",LEN(TRIM(E$66)))), (1-1)*LEN(TRIM(E$66))+1, LEN(TRIM(E$66)))),"")
G67	G67	=IFERROR(VALUE(LEFT(AIS08_output,1)),"")
D68:F68	E68	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E$66),";",REPT(" ",LEN(TRIM(E$66)))), (2-1)*LEN(TRIM(E$66))+1, LEN(TRIM(E$66)))),"")
G68	G68	=IFERROR(VALUE(MID(AIS08_output,3,1)),"")
D69:F69	E69	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E$66),";",REPT(" ",LEN(TRIM(E$66)))), (3-1)*LEN(TRIM(E$66))+1, LEN(TRIM(E$66)))),"")
G69	G69	=IFERROR(VALUE(MID(AIS08_output,5,1)),"")
D70:F70	E70	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E$66),";",REPT(" ",LEN(TRIM(E$66)))), (4-1)*LEN(TRIM(E$66))+1, LEN(TRIM(E$66)))),"")
G70	G70	=IFERROR(VALUE(MID(AIS08_output,7,1)),"")
D71:F71	E71	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E$66),";",REPT(" ",LEN(TRIM(E$66)))), (5-1)*LEN(TRIM(E$66))+1, LEN(TRIM(E$66)))),"")
G71	G71	=IFERROR(VALUE(MID(AIS08_output,9,1)),"")
D72	D72	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F$66),";",REPT(" ",LEN(TRIM(F$66)))), (6-1)*LEN(TRIM(F$66))+1, LEN(TRIM(F$66)))),"")
D73	D73	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F$66),";",REPT(" ",LEN(TRIM(F$66)))), (7-1)*LEN(TRIM(F$66))+1, LEN(TRIM(F$66)))),"")
D74	D74	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F$66),";",REPT(" ",LEN(TRIM(F$66)))), (8-1)*LEN(TRIM(F$66))+1, LEN(TRIM(F$66)))),"")
D75	D75	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F$66),";",REPT(" ",LEN(TRIM(F$66)))), (9-1)*LEN(TRIM(F$66))+1, LEN(TRIM(F$66)))),"")
D76	D76	=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F$66),";",REPT(" ",LEN(TRIM(F$66)))), (10-1)*LEN(TRIM(F$66))+1, LEN(TRIM(F$66)))),"")
D100:AH106	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)),"")
Dynamic array formulas.

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

 

[tkmrxlsx]

And photo:

 

[Fluff]

Thanks for that, with that data what are you trying to do?

 

[tkmrxlsx]

It's for building an analysis engine for government databases. Just a quicker way to evaluate things....

 

[Fluff]

But what do you need to calculate?

 

[tkmrxlsx]

`cell F42: count how often, from G42# that H42*I42*J42 =TRUE occurs in the filtered NTable (D100#)

 

[Fluff]

I'm afraid that still doesn't make any sense to me, as none of the values in G:J42 occur in row 100

 

[tkmrxlsx]

The table posted was created to help me figure this out. The one I'm working on runs out to column FT.
re-post:

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
47		Left
48			detail_ASPECT90	detail_SYSORG	detail_LESION
49	0	Head	Left	Brain	Injured, Unknown Lesion
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	Front Left Side;Front Middle	Head	2;3;4;5;6	PDOF range	Delta-V range
67			Angle	Front Left Side	Head	2	>=0	>=0
68				Front Middle		3	<=45	<=50
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-1	1151	Clear	Angle	Dry	89	1	270	350	59	2	1	Front Left Side	62	Male	Lap And Shoulder	1	Head	Right	Brain	Injured, Unknown Lesion	2	280
101			11-17	I-2	1151	Clear	Angle	Dry	89	1	270	350	59	2	1	Front Left Side	62	Male	Lap And Shoulder	2	Head	Left	Brain	Injured, Unknown Lesion	2	280
102			11-57	I-2	28	Clear	Angle	Dry	72	2	90	335	43	0	1	Front Left Side	51	Female-Not Preg	Lap And Shoulder	2	Head	Whole Region	Brain	Concussion	2	20
103			11-68	I-10	1329	Cloudy	Angle	Dry	80	1	10	250	21	0	1	Front Left Side	84	Female-Not Preg	Lap And Shoulder	10	Head	Whole Region	Brain	Injured, Unknown Lesion	2	40
104			11-68	I-11	1329	Cloudy	Angle	Dry	80	1	10	250	21	0	1	Front Left Side	84	Female-Not Preg	Lap And Shoulder	11	Head	Inferior/Lower	Brain	Injured, Unknown Lesion	5	40
105			3-121	I-1	1040	Clear	Angle	Dry	40	2	320	140		0	1	Front Left Side	56	Male	None Used/Avail	1	Head	Whole Region	Brain	Concussion	2
106			3-93	I-1	1400	Clear	Angle	Dry	40	1	40	80	18	0	1	Front Left Side	48	Male	Lap And Shoulder	1	Head	Whole Region	Brain	Concussion	2	0
HEAD

Cell Formulas
Range	Formula
F47,F50	F47	=IF(G47="","",SUMPRODUCT((G$40=$CD$100:$CD$20000)*(G47=$CE$100:$CE$20000)*(H47=$CF$100:$CF$20000)))
G47:G50	G47	=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:G46))),"")
H47:H50	H47	=IFERROR(INDEX(INDIRECT(H$41),SMALL(IF(((detail_REGION08=$F$67)*(detail_ASPECT90=$G$40)),ROW(INDIRECT(H$41))-MIN(ROW(INDIRECT(H$41)))+1),ROWS($H$41:H46))),"")
B49	B49	=SUMPRODUCT(--(ISNUMBER(MATCH(C$49#>0,0))))
C49:F49	C49	=UNIQUE(FILTER(NTable[[oiooo_a_REGION08]:[oiooo_a_LESION]],(($C$47=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))))
D51	D51	=COUNTA(B100:B$20000)
H51	H51	=IF(I51="","",SUMPRODUCT((G$40=$CD$100:$CD$20000)*(I51=$CE$100:$CE$20000)*(J51=$CF$100:$CF$20000)))
I51	I51	=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:G50))),"")
J51	J51	=IFERROR(INDEX(INDIRECT(H$41),SMALL(IF(((detail_REGION08=$F$67)*(detail_ASPECT90=$G$40)),ROW(INDIRECT(H$41))-MIN(ROW(INDIRECT(H$41)))+1),ROWS($H$41:H50))),"")
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:AA106	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