Dynamic Spilled Arrays - mutliple criteria to sum query

tkmrxlsx

New Member
Joined
Dec 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,668
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

tkmrxlsx

New Member
Joined
Dec 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Yes, really just D49:F49.
F67 specifies "Head" for D100# result
 

tkmrxlsx

New Member
Joined
Dec 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
68Head-OnFront 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
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
HEAD
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
AIS08_output=HEAD!$G$66G67:G71
MANCOLL_output=HEAD!$D$66D67:D71, B72:B76
REGION08_output=HEAD!$F$66D72:D76, F67:F71
SEATPOS_output=HEAD!$E$66C72:C76, E67:E71
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,668
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,826
Messages
5,627,117
Members
416,223
Latest member
RichardHell

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?

Disable AdBlock

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
Top