How to add up the correct answers from the check boxes question?

Question

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

My question is;

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

Answer 1

Two more to try:

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

or

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

Answer 2

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)

Explanation:

• 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)