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

2 Answers 2


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

"SELECT Col1,count(Col1) group by Col1"

enter image description here

  • Looks great, except dealing with the space after the comma. Luckily my sheet is consistent. 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

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:

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


Your Answer

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

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