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

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:


... which will return 2.

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

| improve this answer | |
  • Can you possibly help with with my next problem too?? – alix turley Nov 19 '15 at 2:55
  • This time i am trying to calculate the number of correct answers. Eg: Option 1 -Correct, Option 2 - Incorrect, Option 3 - Correct. The Students need to tick the check boxes, but i still want to award them a mark for each correct answer chosen. Thanks so much for your help on my last question, i has stuck on that for a few days. haha – alix turley Nov 19 '15 at 2:57
  • You should post that as a separate question, with a link to this question. – Vidar S. Ramdal Nov 19 '15 at 7:22

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.