Count cells that contain errors

To count cells that contain errors, you can use the SUMPRODUCT and ISERROR functions with the double negative method.

This tutorial is a part of our Excel formulas guide.

How to count cells that contain errors?

Here are the steps to count cells that contain errors in Excel:

  1. Open Excel.
  2. Type =SUM(–ISERROR(range)), replacing range with your specific cell range.
  3. Press Enter.
  4. The formula will return the count of cells within the specified range that contain errors.

Example

The following example shows a dataset in the range B3:B12, which contains text and potential error values. We want to count the total number of errors in the dataset. The formula in cell D3 uses the ISERROR function to identify errors in the data, creating a logical array where each error is marked as TRUE. The double negative (–) converts the logical values into numeric values, and the SUMPRODUCT function sums these values to calculate the total number of errors.

We recommend you use named ranges to simplify the formula. Select the range, then use the name box and add a descriptive name, like “data.”

Explanation: Evaluate the formula from the inside out!

ISERROR(data): The ISERROR function checks each cell in the specified range, data, to see if it contains any error. Common Excel error types include #VALUE!, #N/A, #DIV/0!, #REF!, #NAME?, and #NULL!. If a cell in the data has an error, ISERROR returns TRUE for that cell. If there is no error, it returns FALSE.

For example, if data contains the values {Yellow, Green, #DIV/0, Green, #NAME?, Blue, #NUM!, Blue, Blue, Green}, then ISERROR(data) will produce {FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE}.

true false iserror

ISERROR(data): The double negative (–) is a technique in Excel to convert TRUE and FALSE values into 1 and 0, respectively. TRUE becomes 1, and FALSE becomes 0. So, applying — to {FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE}} will convert it to {0, 0, 1, 0, 1, 0, 1, 0, 0, 0}

iserror double negative

SUMPRODUCT(–ISERROR(data)): SUMPRODUCT typically multiplies corresponding elements in arrays and then adds up the products. However, we only have one array here, so SUMPRODUCT simply sums the values in that array. The array will be summed to 3. This result represents the count of cells in data that contain an error.