Score quiz answers with key

=SUM(--(answers=key))
To score a quiz and count correct and incorrect answers based on an answer key, you can use a basic array formula. In the example shown, the formula in I7, copied down, is:
=SUM(--(C7:G7=key))
where "key" is the named range C4:G4.
Note: This is an array formula. In Excel 365, enter normally. In older versions of Excel, you must enter with control + shift + enter.
This formula uses the named range "key" (C4:G4) for convenience only. Without the named range, you'll want to use an absolute reference so the formula can be copied.
In cell I7, we have this formula:
=SUM(--(C7:G7=key))
working from the inside-out, this expression is evaluated first:
C7:G7=key // compare answers to key
The result is an array of TRUE FALSE values like this:
{TRUE,TRUE,TRUE,FALSE,TRUE}
TRUE values indicate a correct answer, FALSE values indicate an incorrect answer.
To coerce the TRUE and FALSE values to numbers, we use a double negative:
--({TRUE,TRUE,TRUE,FALSE,TRUE}) // get 1's and 0's
The is an array of 1's and 0's delivered directly to the SUM function:
=SUM({1,1,1,0,1}) // sum correct
The SUM function then returns the final result, 4.
Incorrect answers
The formula in J7 counts incorrect answers in almost the same way:
=SUM(--(C7:G7<>key))
The only difference is the we are now using the not equal to (<>) logical operator:
Percent correct
The formula in K7 calculates the percentage of correct answers like this:
=I7/COUNTA(key) // divide correct by total
Cell I7 already contains the count of correct answers. This is divided by the total count of quiz answers, which is calculated with the COUNTA function:
COUNTA(key) // count total
The result is formatted with the percentage number format.
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.