What syntax do I use when adding up the number of correct answers that have boxes checked?

My question is;enter image description here

In this example the correct answers are Cotton and Calico. I award the students a total of 2 marks ( 1 mark each) in selected. Therefore if a student selects Denim, Satin or Silk, they do not get a mark.

The current Syntax I have been using is something along the lines of;

=if(D2="Cotton.", "1", "0", "Calico.", "1", "0", "Demin.", "0", "0", "Satin.", "0", "0", "Silk.", "0", "0")
  • For thos eof you who might be looking at the other side of the question this answer was great help ; webapps.stackexchange.com/questions/86804/… – alix turley Nov 19 '15 at 8:01
  • So, if a student selects all five boxes, do they get full credit (2 points) as well? That seems wrong. – user79865 Nov 19 '15 at 14:36
  • @NormalHuman, The students are only allowed to select 2 answers. – alix turley Nov 23 '15 at 7:37

Two more to try:

=SUM(ArrayFormula(--regexmatch(split(D2,","), "Cotton|Calico")))


=SUM(ArrayFormula(countif(D2, {"*Cotton*", "*Calico*"})))

The responses are recorded in the comma-space separated format, e.g., Calico, Silk. Suppose that the response is in cell B2: then the following formula gives the number of correctly checked boxes:

=countif(arrayformula(regexmatch(split(B2,", "), "^(Cotton|Calico)$")), TRUE)


  • split(B2,", ") splits the string into an array of responses
  • arrayformula(regexmatch(..., "^(Cotton|Calico)$")) processes the responses, returning TRUE if it's correct.
  • countif(... TRUE) counts the number of TRUE results.

If the correct answers are stored in, say, cells E2:E3, then the formula becomes

=countif(arrayformula(regexmatch(split(B2,", "), "^("&join("|", E2:E3)&")$")), TRUE)

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.