Count cells with errors
This tutorial shows how to count the number of cells in a specified range that contain errors using an Excel formula, with the SUMPRODUCT and ISERROR functions
|
=SUMPRODUCT(--ISERROR(B5:B9))
|
GENERIC FORMULA
=SUMPRODUCT(--ISERROR(range))
ARGUMENTS EXPLANATION For each cell in the selected range, the ISERROR function identifies the cells that contain an error. Then the SUMPRODUCT function is used to sum the number of cells that the ISERROR function identified as containing an error.
In this example the range from which we are counting the total number of cells that contain an error is B5:B9. The formula returns a value of 3 as the cells within that range that contain an error are B6, B7 and B9. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count total number of words in a string | How to count the total number of words in a string | |
| Count cells that do not have errors | How to count the number of cells in a specified range that do not contain errors |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values |