Arranging data by occurrences

Question

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!

Answer 1

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)), " "))})

---

update:

=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}", )})