0

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: enter image description here

  • 1
    Hey, could you share some sample data in a dummy sheet ? could be helpful for us to better understand the problem – pjmg Feb 21 at 17:24
  • 1
    Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Feb 21 at 20:52
  • @pjmg Any thoughts? – Costa Feb 25 at 21:35
  • @marikamitsos any thoughts? – Costa Feb 25 at 21:36
  • Not really. Had a look at your sheet just now trying to understand what you are looking for but it was just a replica of your screenshot. I still don't see it. I must be missing something. – marikamitsos Feb 26 at 4:17
1

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

enter image description here

| improve this answer | |
  • Looks great, except dealing with the space after the comma. Luckily my sheet is consistent. – Costa Feb 26 at 19:14
  • 1
    @Costa you can try to add =trim() or replace them with a comma like this regexreplace("range"," ",",") – pjmg Feb 27 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.