Google Sheets – Formula for Checkboxes Question in Forms

google sheetsgoogle-forms

I am creating a Google Spreadsheet for the first time and I have suddenly become stuck.

When filling out my spreadsheet I do not know what formula to use when adding up the correct answers.

I have included an example below (all the answers are correct, however if my student only selects one, I still want to give them a mark to each allocated tick)

enter image description here

The syntax I am currently working on is:

=if(L2="Return all unused pins back into the plastic kits.", "1", "0")+(L2="Use fabric scissors when cutting  fabric.", "1", "0")+(L2="Position the sewing machine close to you when you are working", "1", "0")+(L2="Return all equipment back to where it belongs.", "1", "0")+(L2="Place your work inside the CORRECTLY labeled class tub.", "1", "0")+(L2="Turn iron off at power point", "1", "0")+(L2="Push chairs under desk when leaving work station", "1", "0")+(L2="Use polite manners at all times.")

Best Answer

It seems what you want to do in your example is to count the number of options ticked.

In the spreadsheet receiving the form responses, each response is assigned to a row. The answers to your security questions are assigned to a cell in that row, apparently L2, judging by your formula example.
If a student selects, say, the first two options, the L2 cell will be Return all unused pins back into the plastic kits., Use fabric scissors when cutting fabric.. That is, the text of the selected options, separated by , (a comma and a space).

Knowing that, we can put the following formula in another cell in that row (for example in M2):

=SPLIT(L2; ", "; FALSE)

This will expand the contents of L2, splitting the string up by the , separator, so that each checked option will occupy its own cell:

| L2                                                 | M2                      | N2                        | O2 | ... |
-----------------------------------------------------------------------------------------------------------------------
| Return all unused (...), Use fabric scissors (...) | Return all unused (...) | Use fabric scissors (...) |    |

... which is not exactly what we want, since we just want to know the number of checked options, but we can learn that by wrapping the formula in a COUNTA function:

=COUNTA(SPLIT(L2; ", "; FALSE))

... which will return 2.

Feel free to copy the spreadsheet I set up to demonstrate.

Related Topic