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

#### Fluff

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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### tkmrxlsx

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

#### tkmrxlsx

##### New Member
Another example:
dCDS_2015_InjuryDetail.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAA
36Whole region
37detail_ASPECT90detail_SYSORGdetail_LESION
380ThoraxLeft SkeletalFracture
39ThoraxLeftPulmonary - Lungs Laceration
40ThoraxCentralArteries - Veins Laceration
41ThoraxWhole RegionInjured, Unknown Injured, Unknown Lesion
42
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
66Angle;Head-OnFront Left Side;Front MiddleThorax2;3;4;5;6PDOF rangeDelta-V range
67AngleFront Left SideThorax2>=0>=0
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-41151ClearAngleDry8912703505921Front Left Side62MaleLap And Shoulder4ThoraxBilateral SkeletalFracture3280
10111-17I-51151ClearAngleDry8912703505921Front Left Side62MaleLap And Shoulder5ThoraxBilateralPulmonary - LungsContusion3280
10211-17I-61151ClearAngleDry8912703505921Front Left Side62MaleLap And Shoulder6ThoraxBilateralPulmonary - Lungs Laceration4280
10311-17I-71151ClearAngleDry8912703505921Front Left Side62MaleLap And Shoulder7ThoraxBilateralInjured, Unknown Injured, Unknown Lesion3280
10411-17I-81151ClearAngleDry8912703505921Front Left Side62MaleLap And Shoulder8ThoraxCentral5280
10511-17I-91151ClearAngleDry8912703505921Front Left Side62MaleLap And Shoulder9ThoraxCentralArteries - Veins Laceration4280
10611-17I-121151ClearAngleDry8912703505921Front Left Side62MaleLap And Shoulder12ThoraxLeftRespiratory Rupture4280
10711-3I-253CloudyHead-OnWet7213401759121Front Left Side21Female-Not PregNone Used/Avail2ThoraxLeft SkeletalFracture30
10811-3I-353CloudyHead-OnWet7213401759121Front Left Side21Female-Not PregNone Used/Avail3ThoraxLeftPulmonary - Lungs Laceration40
10911-3I-453CloudyHead-OnWet7213401759121Front Left Side21Female-Not PregNone Used/Avail4ThoraxCentralArteries - Veins Laceration40
11011-3I-553CloudyHead-OnWet7213401759121Front Left Side21Female-Not PregNone Used/Avail5ThoraxWhole RegionInjured, Unknown Injured, Unknown Lesion30
11111-68I-21329CloudyAngleDry801102502101Front Left Side84Female-Not PregLap And Shoulder2ThoraxRight SkeletalFracture340
11211-68I-31329CloudyAngleDry801102502101Front Left Side84Female-Not PregLap And Shoulder3ThoraxCentral SkeletalFracture240
11311-68I-91329CloudyAngleDry801102502101Front Left Side84Female-Not PregLap And Shoulder9ThoraxBilateralPulmonary - LungsContusion340
Cell Formulas
RangeFormula
F36F36=IF(G47="","",SUMPRODUCT((G\$40=\$CD\$100:\$CD\$20000)*(G47=\$CE\$100:\$CE\$20000)*(H47=\$CF\$100:\$CF\$20000)))
B38B38=SUMPRODUCT(--(ISNUMBER(MATCH(C\$38#>0,0))))
C38:F41C38=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))))
G50G50=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))),"")
D51D51=COUNTA(B100:B\$20000)
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:AA113D100=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

#### Fluff

##### MrExcel MVP, Moderator
I think you will need to stick to your original formula, or use countifs
Excel Formula:
``=COUNTIFS(W100:W1000,D49,X100:X1000,E49,Y100:Y1000,F49)`` Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,657
Messages
5,838,635
Members
430,558
Latest member
Krampus ### 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.

### Which adblocker are you using?    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

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