Google-sheets – How to make a bar chart that parses comma separated arrays in Google sheets

google sheets

I have a Google form with a multiple choice question. In the Google sheet, it places all of the checked responses in one cell separated by commas.

I'd like to generate a chart that counts the instances of each response across a whole set of responses, whole column, but it takes each combination of comma separated choices as unique for my bar chart.

What to do?

Editable Sheet: https://docs.google.com/spreadsheets/d/13EXYuSmWteaASzg0qP_hG_8uW2UNDmlX40IPXNGkVLU/edit#gid=0

Screenshot:

Best Answer

Here's a way to split each values and count how many occurences of a unique reference exists :

=query(
transpose(split(JOIN(",",A5:A13),",",true,true)),
"SELECT Col1,count(Col1) group by Col1"
)

Related Solutions

Google-sheets – How to paste a comma separated string over multiple cells in Google Sheets

You can paste it into a split function in a cell and it will break it apart into multiple cells for you:

=SPLIT("l,f,x,a,s,f", ",")

Google-sheets – A chart with multiple series encoded by comma-separated names

One approach is to separate the data into three sets using three query functions such as

=query(A:C, "select A, sum(C) where B contains 'A' group by A", 1)

But this has a drawback: the dates for which 'A' has no data are omitted instead of having 0. A modified approach is to preprocess the values with arrayformula. For example, in D2:

=arrayformula(if(iserror(find("A", B2:B)), 0, C2:C))

which outputs the content of C if "A" is found in column B, and 0 otherwise. Similarly in E2 and F2 for two other names. Finally, the query

=query(A:F, "select A, sum(D), sum(E), sum(F) where A is not null group by A", 1)

will group the values in D, E, F by date, with this output:

+-----+---+---+---+
| 1/1 | 4 | 9 | 8 |
| 1/2 | 3 | 1 | 3 |
| 1/3 | 0 | 2 | 2 |
+-----+---+---+---+

Best Answer

Related Solutions

Google-sheets – How to paste a comma separated string over multiple cells in Google Sheets

Google-sheets – A chart with multiple series encoded by comma-separated names

Related Topic