3

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

Improve this question
5
  • 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, 2020 at 17:24
  • 1
    Please read how to and share a test sheet so as you can be easier helped.
    – marikamitsos
    Feb 21, 2020 at 20:52
  • @pjmg Any thoughts?
    – Costa Michailidis
    Feb 25, 2020 at 21:35
  • @marikamitsos any thoughts?
    – Costa Michailidis
    Feb 25, 2020 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, 2020 at 4:17

2 Answers 2

Reset to default
5

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
2
  • Looks great, except dealing with the space after the comma. Luckily my sheet is consistent.
    – Costa Michailidis
    Feb 26, 2020 at 19:14
  • 1
    @Costa you can try to add =trim() or replace them with a comma like this regexreplace("range"," ",",")
    – pjmg
    Feb 27, 2020 at 9:59
3

Here is a solution based on the answer from pjmg that also deals with trailing space in the comma separated list correctly, and also sorts from high to low:

=sort(
   query(
transpose(ArrayFormula(trim(split(JOIN(",",'Ranked candidates'!V2:V999),",",true,true)))),
"SELECT Col1,count(Col1) group by Col1"
   ),
   2, false)

More about ArrayFormula and Trim

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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