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!
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,666
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Added spreadsheet capture:

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
67AngleFront Left SideHead2>=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
99PSU-a-CASENOCOI_aaccid_a_TIMEgvooo_a_CLIMATEaccid_a_MANCOLLgvooo_a_SURCONDgvooo_a_SPLIMITgvooo_a_VEHNO
10011-17I-11151ClearAngleDry891
HEAD
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
AIS08_output=HEAD!$G$66G67:G71
MANCOLL_output=HEAD!$D$66D67:D71
REGION08_output=HEAD!$F$66D72:D76, F67:F71
SEATPOS_output=HEAD!$E$66E67:E71
 

tkmrxlsx

New Member
Joined
Dec 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
And photo:
1608579506543.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,666
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

tkmrxlsx

New Member
Joined
Dec 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
It's for building an analysis engine for government databases. Just a quicker way to evaluate things....
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,666
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

But what do you need to calculate?
 

tkmrxlsx

New Member
Joined
Dec 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
`cell F42: count how often, from G42# that H42*I42*J42 =TRUE occurs in the filtered NTable (D100#)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,666
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
67AngleFront Left SideHead2>=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
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
HEAD
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
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,820
Messages
5,627,087
Members
416,219
Latest member
TommyBoy79

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