Count checkbox answers from a Google Form™ (COUNTIF on multi-select)
COUNTIF gives the wrong number on a checkbox question, one way or the other. Here is a formula that counts each option exactly, and a table of every option with its percentage.
Why COUNTIF gets it wrong
A checkbox question puts every ticked option in one cell:
Vegetarian, Gluten free. Say the answers are in column D of
Form Responses 1.
=COUNTIF(D2:D, "Vegetarian")This undercounts. It only counts cells that are exactly “Vegetarian”, so everyone who also ticked something else is left out.
=COUNTIF(D2:D, "*Vegetarian*")
This overcounts. It matches “Vegetarian” anywhere in the cell,
including inside “Non-vegetarian”, and COUNTIF ignores case. If an
option contains ? or *, those act as wildcards too.
A formula that counts exactly
=SUMPRODUCT(--ISNUMBER(FIND(", Vegetarian,", ", " & D2:D & ",")))
Each answer is wrapped as , Vegetarian, Gluten free, and searched for
, Vegetarian,, so the option only matches as a whole item.
FIND is case-sensitive and has no wildcards, which is what you want here: Forms
always writes an option exactly as it appears on the form. Blank answers never match, so
people who skipped the question are not counted.
Every option in one table
On a new tab, list the options in column A (A2 down) and put this in B2, then fill it down:
=SUMPRODUCT(--ISNUMBER(FIND(", " & A2 & ",", ", " & 'Form Responses 1'!$D$2:$D & ",")))For a percentage of the people who answered the question, in C2:
=B2 / COUNTA('Form Responses 1'!$D$2:$D)Format column C as a percentage. The percentages will add up to more than 100%, and that is correct. People could tick more than one box, so each percentage answers “what share of respondents ticked this?”. If you want a pie chart, this is the wrong data for it. Use a bar chart.
Getting the option list without typing it
=SORT(UNIQUE(FLATTEN(ARRAYFORMULA(IFERROR(SPLIT(D2:D, ", ", FALSE), "")))))That splits every answer, stacks the pieces into one column and removes repeats. Check the result before you trust it:
- An option with a comma in it (“Tea, coffee”) comes out as two entries.
- Whatever people typed under “Other” shows up as extra entries, one per spelling.
- An option nobody has ticked yet will not appear at all.
The list on the form itself is the real one. Copy it from there if you can.
Counting by group
To count vegetarians per session (session in column C), add a condition inside the
SUMPRODUCT:
=SUMPRODUCT(('Form Responses 1'!$C$2:$C = "Morning") *
ISNUMBER(FIND(", Vegetarian,", ", " & 'Form Responses 1'!$D$2:$D & ",")))For many groups and many options, it is easier to split the answers into one TRUE/FALSE column per option first and then use a pivot table.
The one-click version
The add-on writes a Summary tab with every option counted exactly and as a percentage of the people who answered. Options that contain commas are handled, and whatever people typed under Other gets its own column.
Form Response Cleaner is a Google Sheets™ add-on: free for up to 250 responses, and your responses tab is never touched.
More on Google Form™ responses
- How to split Google Form™ checkbox responses into separate columns
- Remove duplicate Google Form™ submissions, keeping the latest
- Google Form™ responses: shorten long question headers without breaking the form
- Phone numbers losing the leading zero in Google Form™ responses
- Why you shouldn't sort or edit the Form Responses tab (and what to do instead)
- The guide: every rule, with the by-hand formula
Google, Google Forms™, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Form Response Cleaner is not affiliated with or endorsed by Google.