You often will see different errors when working with a worksheet. There are seven common errors in Excel, each caused by various reasons: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.
| Error | Description |
|---|---|
| #N/A | A value is not available |
| #VALUE! | A different value type |
| #REF! | A reference cell no longer exists |
| #DIV/0! | A number divides by zero or a blank cell |
| #NUM! | A formula with invalid numeric values |
| #NAME? | An incorrect range or function name |
| #NULL! | An incorrect intersection operator in a formula |
Example: You are working on a worksheet with the first name in column A and the last name in column B. Some cells are filled with errors.
Formula: How many cells are with errors in the range of A2:B12?
To determine the number of cells with errors, you need to use the ISERROR function and the SUMPRODUCT function. The ISERROR function checks whether a cell is an error, while the SUMPRODUCT function returns the product of numbers in an array.
=SUMPRODUCT(–ISERROR(A2:B12))
=SUMPRODUCT(ISERROR(A2:B12)*1)
Explanation:
- Step 1: ISERROR(A2:B12): The ISERROR function returns true if the value is an error; otherwise, it returns false;
- Step 2: The double hyphens (or times 1) are to convert true to one and false to zero;
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array.
Please note that three similar functions can be used for error checking.
- The ISERROR function refers to any error value;
- The ISERR function refers to any error value except #N/A; and
- The ISNA function refers to the #N/A error value.
For example, if you want to count the cells with errors except for #N/A, you can use the following formulas, and the result will return 6 in this example (excluding the #N/A error in Cell A3).
=SUMPRODUCT(–ISERR(A2:B12))
=SUMPRODUCT(ISERR(A2:B12)*1)
If you only need to count the number of cells that contain one specific error, you can use the COUNTIF function. For example, the number of cells with the #NULL! error.
=COUNTIF(A2:B12,"#NULL!")