I have the following formula:

=ARRAYFORMULA(QUERY(IFERROR(FLATTEN(SPLIT(FLATTEN(REPT(REGEXEXTRACT(SPLIT(A1:A4, " "), 
 "(?:\d x)?(. )")&"×", IFERROR(REGEXEXTRACT(SPLIT(A1:A4, " "), "(\d )x"), 1)*1)), "×"))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''"))

This counts the number of entries in column A, extracting the variable text like 2x and 1x, then lists them as unique cells accompanied by a count of each (while ignoring empty cells).

Now, I'd like to modify this to count associated checkboxes in 3 previous columns, comparing with the unique entries of the original column, as well as comparing the variable number of 2x and 1x so that it appropriately assigns the values.

In other words, I want it to turn out like so:

I've tried to modify the previous formula by adding (what I thought was) an additional range to the second FLATTEN.

=ARRAYFORMULA(QUERY(IFERROR(FLATTEN(SPLIT(FLATTEN(REPT(REGEXEXTRACT(SPLIT(D1:D14, " "), 
 "(?:\d x)?(. )")&"×", IFERROR(REGEXEXTRACT(SPLIT(D1:D14, " "), "(\d )x"), 1)*1)), "×"),
SPLIT(REPT(COUNTIF(A1:A=true,D4:D<>"")*1), "×"))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 order by count(Col1) DESC label count(Col1)''"))

Among many others as I've begun to understand the formula given, I also tried a very roundabout way, first removing the QUERY part of the above to separate the Unique Strings into one column, and count into the other:

=FLATTEN(REPT(COUNTIF(B1:C=true,D1:D=F1),IFERROR(REGEXEXTRACT(SPLIT(N4:N56, " "), "(\d )x"), 1)*1))

None of those worked.

So again, how would I get the following results?

CodePudding user response：

try:

=ARRAYFORMULA(QUERY(""&IFERROR(FLATTEN(SPLIT(FLATTEN(
 IF((A1:C15=TRUE)*NOT(REGEXMATCH(D1:D15, "\d x")), D1:D15, 
 IF(REGEXMATCH(D1:D15, "\d x"), REPT(REGEXEXTRACT(SPLIT(D1:D15, " "), "\d x(.*)")&"×", 
 REGEXEXTRACT(SPLIT(D1:D15, " "), "(\d )x")*1), ))), "×"))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 
  order by count(Col1) DESC label count(Col1)''"))