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:
- Open Excel.
- Type =SUM(–ISERROR(range)), replacing range with your specific cell range.
- Press Enter.
- The formula will return the count of cells within the specified range that contain errors.
Example
In the example, we have a range of cells (B3:B12) that contain some errors. The goal is to count the cells containing various 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}.
—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}
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.