Given a range with multiple items, I would like to count their occurrences and present the result as shown in this Google Spreadsheet sample using just one formula, if possible.
This is the closest that I get:
=ARRAYFORMULA(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},"select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''",0),"select Col1, Count(Col1) group by Col1 pivot Col2",0))
Any thoughts or ideas would be appreciated, thank you!
try:
=ARRAYFORMULA(TRANSPOSE(LEN(SUBSTITUTE(FLATTEN(QUERY(QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
"select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''", ),
"select max(Col1) group by Col1 pivot Col2", ), "offset 1", ),,9^9)), " ", ))))
and:
=ARRAYFORMULA(TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
"select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''", ),
"select max(Col1) group by Col1 pivot Col2", ),,9^9)), " ")))
or joint:
=ARRAYFORMULA({TRANSPOSE(LEN(SUBSTITUTE(FLATTEN(QUERY(QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
"select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''", ),
"select max(Col1) group by Col1 pivot Col2", ), "offset 1", ),,9^9)), " ", )));
TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
"select Col1, count(Col2) where Col1 <> '' group by Col1 label count(Col2) ''", ),
"select max(Col1) group by Col1 pivot Col2", ),,9^9)), " "))})
=ARRAYFORMULA({TRANSPOSE(LEN(SUBSTITUTE(FLATTEN(QUERY(IF(""=QUERY(QUERY(QUERY(FLATTEN(B3:U11)&{"",""},
"select Col1, count(Col2) where Col1 is not null group by Col1 label count(Col2) ''", ),
"select max(Col1) group by Col1 pivot Col2", ), "offset 1", ),,"×"),,9^9)), " ", )));
REGEXREPLACE(""&TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(QUERY(FLATTEN(
IF(ISNUMBER(IFERROR(1/(1/(1*B3:U11)))), "!"&TEXT(B3:U11, "000000000#"), B3:U11))&{"",""},
"select Col1, count(Col2) where Col1 is not null group by Col1 label count(Col2) ''", ),
"select max(Col1) group by Col1 pivot Col2", ),,9^9)), " ")), "^!0{1,9}", )})
