ARRAYFORMULA not expanding

1
votes

I have the following ARRAYFORMULA entered into cell D2, and it seems like it should be expanding downward throughout the entire column (I'll do the "check for blank A2" addition to the formula once this part works correctly), but nothing is expanding. Where have I gone astray?

Here's the formula:

=ArrayFormula(IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(E) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (E) Contains 'Option' label count(E) ''"),0)+
IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(F) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (F) Contains 'Option' label count(F) ''"),0)+
IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(G) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (G) Contains 'Option' label count(G) ''"),0)+
IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(H) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (H) Contains 'Option' label count(H) ''"),0)+
IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(I) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (I) Contains 'Option' label count(I) ''"),0))

enter image description here

A link to a copy of the spread follows: https://docs.google.com/spreadsheets/d/14E1QEfcTYiwOG_gORkc8YoRuWEPM7FweLljj0hQCQTc/edit?usp=sharing

1
google-sheetsarray-formulas
because 2nd parameter of query does not support arrays. share a copy of your sheet – player0
thank you! I added link to a copy of the spread to the end of the original post – justbriman

1 Answers

1
votes

try:

=ARRAYFORMULA(IF(TRIM(B2:B)="",,IFNA(IFNA(VLOOKUP((B2:B), 
 QUERY({IFNA(IFNA(REGEXEXTRACT('Form Responses 1'!C2:C, "\b"&TEXTJOIN("\b|\b", 1, (B2:B))&"\b"), 
 REGEXEXTRACT('Form Responses 1'!C2:C, "\b"&TEXTJOIN("\b|\b", 1, TRIM(A2:A))&"\b"))), 
 MMULT(N(REGEXMATCH('Form Responses 1'!E2:I, "(?i)Option")), {1;1;1;1;1})}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, 0), VLOOKUP(TRIM(A2:A), 
 QUERY({IFNA(IFNA(REGEXEXTRACT('Form Responses 1'!C2:C, "\b"&TEXTJOIN("\b|\b", 1, (B2:B))&"\b"), 
 REGEXEXTRACT('Form Responses 1'!C2:C, "\b"&TEXTJOIN("\b|\b", 1, TRIM(A2:A))&"\b"))), 
 MMULT(N(REGEXMATCH('Form Responses 1'!E2:I, "(?i)Option")), {1;1;1;1;1})}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, 0)), 0)))

enter image description here

UPDATE:

=ARRAYFORMULA(IF(C2:C="",,IFNA(IFNA(VLOOKUP(B2:B, QUERY({TRIM(FLATTEN(QUERY(TRANSPOSE(
 REGEXEXTRACT('Form Responses 1'!C2:C, TEXTJOIN("|", 1, "("&SUBSTITUTE(TRIM(UNIQUE(
 FILTER({Sheet3!B2:B; Sheet3!A2:A}, {Sheet3!B2:B; Sheet3!A2:A}<>""))), " ", ").+(")&")"))),,9^9))) , 
 MMULT(N(REGEXMATCH('Form Responses 1'!E2:I, "(?i)Option")), {1;1;1;1;1})}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, 0), 
 VLOOKUP(A2:A, QUERY({TRIM(FLATTEN(QUERY(TRANSPOSE(
 REGEXEXTRACT('Form Responses 1'!C2:C, TEXTJOIN("|", 1, "("&SUBSTITUTE(TRIM(UNIQUE(
 FILTER({Sheet3!B2:B; Sheet3!A2:A}, {Sheet3!B2:B; Sheet3!A2:A}<>""))), " ", ").+(")&")"))),,9^9))) , 
 MMULT(N(REGEXMATCH('Form Responses 1'!E2:I, "(?i)Option")), {1;1;1;1;1})}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, 0)), 0)))