2

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!

zagalico
  • 35
  • 4

1 Answers1

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you. Only with text it works perfectly, but if some items are numbers the result gets messed up. Can you please modify the formula having into account the presence of also numbers, showing the result in an ascending order (for both letters and numbers)? – zagalico Jul 30 '21 at 08:33
  • Great! Thank you! It sorts the result, however the count is not right. – zagalico Jul 31 '21 at 15:30
  • Using the first part of your formula: =ARRAYFORMULA({TRANSPOSE(LEN(TRIM(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)))) - LEN(TRIM(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)), " ", )))+1 ); Please let me know if there is a faster way. – zagalico Jul 31 '21 at 15:31
  • I apologize in advance if I should create a new question. If so, please let me know. I need to present the zero-count in the result. There is a new sheet called "Desired result_2" for clarification. – zagalico Aug 02 '21 at 17:35