Split columns including comma separated values into rows + count occurrences of each value (parsing raw Google Form results)

Question

I'm trying to split a large number of columns in Google Sheets, where some of them include a variable number of comma separated values.

Here's a simplified version of what I'm trying to accomplish.

I want to take a spreadsheet like this one (out of a Google Form):

| Name       | Qualities                                    | Colors         | Origins  |
+------------+----------------------------------------------+----------------+----------+
| apple      | sweet,hard,round, this is my favorite fruit  | red, green     | gb, us   |
| banana     | soft,small                                   | yellow, brown  | us       |
| orange     | round,healthy, sour                          | orange         | nl       |
| watermelon | round, healthy                               | green, red     | us       |

And transform it into this:

| Name                      | Count |
+---------------------------+-------+
| apple                     |1      |
| banana                    |1      |
| orange                    |1      |
| watermelon                |1      |
+---------------------------+-------+
| Qualities                 |       |
+---------------------------+-------+
| round                     |3      |
| healthy                   |2      |
| hard                      |1      |
| small                     |1      |
| soft                      |1      |
| sour                      |1      |
| this is my favorite fruit |1      |
+---------------------------+-------+
| Colors                    |       |
+---------------------------+-------+
| red                       |2      |
| green                     |2      |
| brown                     |1      |
| orange                    |1      |
| yellow                    |1      |
+---------------------------+-------+
| Origins                   |       |
+---------------------------+-------+
| us                        |3      |
| gb                        |1      |
| nl                        |1      |

Here is the same as a screenshot

I have tried this and similar answers, none of which did exactly what I need. I wish I had more to show in ways of code examples, but so far nothing I tried to make myself was even remotely close to helpful.

I'm open to solutions involving draggable formulas or even custom scripts. Any pointers will be appreciated really :)

Answer 1

try:

=INDEX({
 QUERY(FLATTEN(TRIM(IFERROR(SPLIT(A:A, ",")))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc label count(Col1)'Count'", 1);
 QUERY(FLATTEN(TRIM(IFERROR(SPLIT(B:B, ",")))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc label count(Col1)''", 1);
 QUERY(FLATTEN(TRIM(IFERROR(SPLIT(C:C, ",")))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc label count(Col1)''", 1);
 QUERY(FLATTEN(TRIM(IFERROR(SPLIT(D:D, ",")))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc label count(Col1)''", 1)})