Count cells that contain errors

The SUMPRODUCT function accepts one or more arrays, multiplies the arrays together, and returns the "sum of products" as a final result. If only one array is supplied, SUMPRODUCT simply returns the sum of items in the array.

In the example shown, the goal is to count errors in a given range. The formula in E5 is:

=SUMPRODUCT(--ISERROR(B5:B9))

Working from the inside out, the ISERROR function returns TRUE when a cell contains an error, and FALSE if not. Because there are five cells in the range B5:B9, ISERROR evaluates each cell and returns five results in an array like this:

{TRUE;FALSE;TRUE;FALSE;TRUE}

To coerce the TRUE/FALSE values to 1's and 0's, we use a double negative (--). The resulting array looks like this:

{1;0;1;0;1}

Finally, SUMPRODUCT sums the items in this array and returns the total, which is 3 in this case.

ISERR option

The ISERROR function counts all errors. If for some reason you want to count all errors except #N/A, you can use the ISERR function instead:

=SUMPRODUCT(--ISERR(B5:B9)) // returns 2

Since one of the errors shown in the example is #N/A, the ISERR option returns 2 instead of 3.

Array formula with SUM

You can also use the SUM function to count errors, but you must enter as an array formula. Once entered the formula will look like this:

{=SUM(--ISERROR(range))}

The curly brackets are added automatically by Excel and indicate an array formula.

Note: this is an array formula, and must be entered with control + shift + enter, except in Excel 365.