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. In cell I7, we have this formula: working from the inside-out, this expression is evaluated first: The result is an array of TRUE FALSE values like this: 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: The is an array of 1’s and 0’s delivered directly to the SUM function: The SUM function then returns the final result, 4.
Incorrect answers
The formula in J7 counts incorrect answers in almost the same way: The only difference is that we are now using the not equal to (<>) logical operator:
Percent correct
The formula in K7 calculates the percentage of correct answers like this: 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: The result is formatted with the percentage number format.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
