Exceljet

Quick, clean, and to the point

Count cells that contain errors

Excel formula: Count cells that contain errors
Generic formula 
=SUMPRODUCT(--ISERROR(range))
Summary 

To count cells that contain errors, you can use the ISERROR function, wrapped in the SUMPRODUCT function. In the example shown, E5 cell contains this formula:

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

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.