# Dynamic Spilled Arrays - mutliple criteria to sum query

#### tkmrxlsx

##### New Member
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!

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Fluff

##### MrExcel MVP, Moderator
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

##### New Member

dCDS_2015_InjuryDetail.xlsm
DEFGHIJK
39
40Left
41detail_SYSORGdetail_LESION
42SUMPRODUCT(--(what to enter here? G42#HeadLeftBrain Injured, Unknown Lesion
43
44
45
46
47
48
49
50
51# of records: 0
52
53accid_a_MANCOLLoaooo_a_SEATPOSoiooo_a_REGION08oiooo_a_AIS08veooo_a_PDOF1gvooo_a_DVTOTAL
54
55
56
57
58
59
60
61
62
63
64
65
66AngleFront Left Side;Front MiddleHead2;3;4;5;6PDOF rangeDelta-V range
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
99PSU-a-CASENOCOI_aaccid_a_TIMEgvooo_a_CLIMATEaccid_a_MANCOLLgvooo_a_SURCONDgvooo_a_SPLIMITgvooo_a_VEHNO
10011-17I-11151ClearAngleDry891
Cell Formulas
RangeFormula
G42:J42G42=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))))
D51D51=COUNTA(B100:B\$20000)
D67:F67E67=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E\$66),";",REPT(" ",LEN(TRIM(E\$66)))), (1-1)*LEN(TRIM(E\$66))+1, LEN(TRIM(E\$66)))),"")
G67G67=IFERROR(VALUE(LEFT(AIS08_output,1)),"")
D68:F68E68=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E\$66),";",REPT(" ",LEN(TRIM(E\$66)))), (2-1)*LEN(TRIM(E\$66))+1, LEN(TRIM(E\$66)))),"")
G68G68=IFERROR(VALUE(MID(AIS08_output,3,1)),"")
D69:F69E69=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E\$66),";",REPT(" ",LEN(TRIM(E\$66)))), (3-1)*LEN(TRIM(E\$66))+1, LEN(TRIM(E\$66)))),"")
G69G69=IFERROR(VALUE(MID(AIS08_output,5,1)),"")
D70:F70E70=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E\$66),";",REPT(" ",LEN(TRIM(E\$66)))), (4-1)*LEN(TRIM(E\$66))+1, LEN(TRIM(E\$66)))),"")
G70G70=IFERROR(VALUE(MID(AIS08_output,7,1)),"")
D71:F71E71=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(E\$66),";",REPT(" ",LEN(TRIM(E\$66)))), (5-1)*LEN(TRIM(E\$66))+1, LEN(TRIM(E\$66)))),"")
G71G71=IFERROR(VALUE(MID(AIS08_output,9,1)),"")
D72D72=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F\$66),";",REPT(" ",LEN(TRIM(F\$66)))), (6-1)*LEN(TRIM(F\$66))+1, LEN(TRIM(F\$66)))),"")
D73D73=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F\$66),";",REPT(" ",LEN(TRIM(F\$66)))), (7-1)*LEN(TRIM(F\$66))+1, LEN(TRIM(F\$66)))),"")
D74D74=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F\$66),";",REPT(" ",LEN(TRIM(F\$66)))), (8-1)*LEN(TRIM(F\$66))+1, LEN(TRIM(F\$66)))),"")
D75D75=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F\$66),";",REPT(" ",LEN(TRIM(F\$66)))), (9-1)*LEN(TRIM(F\$66))+1, LEN(TRIM(F\$66)))),"")
D76D76=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(F\$66),";",REPT(" ",LEN(TRIM(F\$66)))), (10-1)*LEN(TRIM(F\$66))+1, LEN(TRIM(F\$66)))),"")
D100:AH106D100=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
NameRefers ToCells

#### tkmrxlsx

##### New Member
And photo: #### Fluff

##### MrExcel MVP, Moderator
Thanks for that, with that data what are you trying to do?

#### tkmrxlsx

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

#### Fluff

##### MrExcel MVP, Moderator
But what do you need to calculate?

#### tkmrxlsx

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

#### Fluff

##### MrExcel MVP, Moderator
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

##### New Member
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
BCDEFGHIJKLMNOPQRSTUVWXYZAA
47Left
48detail_ASPECT90detail_SYSORGdetail_LESION
490HeadLeftBrain Injured, Unknown Lesion
50
51# of records: 0
52
53accid_a_MANCOLLoaooo_a_SEATPOSoiooo_a_REGION08oiooo_a_AIS08veooo_a_PDOF1gvooo_a_DVTOTAL
54
55
56
57
58
59
60
61
62
63
64
65
66AngleFront Left Side;Front MiddleHead2;3;4;5;6PDOF rangeDelta-V range
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
99PSU-a-CASENOCOI_aaccid_a_TIMEgvooo_a_CLIMATEaccid_a_MANCOLLgvooo_a_SURCONDgvooo_a_SPLIMITgvooo_a_VEHNOgvooo_a_ANGTHISgvooo_a_ANGOTHERgvooo_a_DVTOTALoaooo_a_DEATHoiooo_a_OCCNOoaooo_a_SEATPOSoaooo_a_AGEoaooo_a_SEXoaooo_a_MANUSEoiooo_a_INJNOoiooo_a_REGION08oiooo_a_ASPECT90oiooo_a_SYSORGoiooo_a_LESIONoiooo_a_AIS08veooo_a_PDOF1
10011-17I-11151ClearAngleDry8912703505921Front Left Side62MaleLap And Shoulder1HeadRightBrain Injured, Unknown Lesion2280
10111-17I-21151ClearAngleDry8912703505921Front Left Side62MaleLap And Shoulder2HeadLeftBrain Injured, Unknown Lesion2280
10211-57I-228ClearAngleDry722903354301Front Left Side51Female-Not PregLap And Shoulder2HeadWhole RegionBrainConcussion220
10311-68I-101329CloudyAngleDry801102502101Front Left Side84Female-Not PregLap And Shoulder10HeadWhole RegionBrain Injured, Unknown Lesion240
10411-68I-111329CloudyAngleDry801102502101Front Left Side84Female-Not PregLap And Shoulder11HeadInferior/LowerBrain Injured, Unknown Lesion540
1053-121I-11040ClearAngleDry40232014001Front Left Side56MaleNone Used/Avail1HeadWhole RegionBrainConcussion2
1063-93I-11400ClearAngleDry40140801801Front Left Side48MaleLap And Shoulder1HeadWhole RegionBrainConcussion20
Cell Formulas
RangeFormula
F47,F50F47=IF(G47="","",SUMPRODUCT((G\$40=\$CD\$100:\$CD\$20000)*(G47=\$CE\$100:\$CE\$20000)*(H47=\$CF\$100:\$CF\$20000)))
G47:G50G47=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:H50H47=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))),"")
B49B49=SUMPRODUCT(--(ISNUMBER(MATCH(C\$49#>0,0))))
C49:F49C49=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))))
D51D51=COUNTA(B100:B\$20000)
H51H51=IF(I51="","",SUMPRODUCT((G\$40=\$CD\$100:\$CD\$20000)*(I51=\$CE\$100:\$CE\$20000)*(J51=\$CF\$100:\$CF\$20000)))
I51I51=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))),"")
J51J51=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:F67D67=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (1-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
G67G67=IFERROR(VALUE(LEFT(AIS08_output,1)),"")
D68:F68D68=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (2-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
G68G68=IFERROR(VALUE(MID(AIS08_output,3,1)),"")
D69:F69D69=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (3-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
G69G69=IFERROR(VALUE(MID(AIS08_output,5,1)),"")
D70:F70D70=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (4-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
G70G70=IFERROR(VALUE(MID(AIS08_output,7,1)),"")
D71:F71D71=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (5-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
G71G71=IFERROR(VALUE(MID(AIS08_output,9,1)),"")
B72:D72B72=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (6-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
B73:D73B73=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (7-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
B74:D74B74=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (8-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
B75:D75B75=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (9-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
B76:D76B76=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(D\$66),";",REPT(" ",LEN(TRIM(D\$66)))), (10-1)*LEN(TRIM(D\$66))+1, LEN(TRIM(D\$66)))),"")
D100:AA106D100=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
NameRefers ToCells

Replies
0
Views
259
Replies
2
Views
314
Replies
5
Views
239
Replies
4
Views
245
Replies
0
Views
143

### Forum statistics

1,176,137
Messages
5,901,567
Members
434,906
Latest member
Prabhu_Churi ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?    Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back