Explanation
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 that we are now using the not equal to (<>) logical operator:
=SUM(--(C7:G7<>key))
=SUM(--({FALSE,FALSE,FALSE,TRUE,FALSE}))
=SUM({0,0,0,1,0})
=1
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 .
Explanation
The second sheet in the workbook, Sheet2, contains 1000 first names in the range B4:F203.
The COUNTIF function takes a range and a criteria. In this case, we give COUNTIF a range equal to all rows in Sheet2.
Sheet2!1:1048576
Note: an easy way to enter this range is to use the Select All button .
For criteria, we use a reference to C4, which contains “John”. COUNTIF then returns 15, since there are 15 cells in Sheet2 equal to “John”.
Contains vs. Equals
If you want to count all cells that contain the value in C4, instead of all cells equal to C4, add the wildcards to the criteria like this:
=COUNTIF(Sheet2!1:1048576,"*"&C4&"*")
Now COUNTIF will count cells with the substring “John” anywhere in the cell.
Performance
In general, it’s not a good practice to specify a range that includes all worksheet cells. Doing so can cause major performance problems, since the range includes millions and millions of cells. When possible, restrict the range to a sensible area.
