Site icon ExcelNotes

How to Count Cells with Errors

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!.

ErrorDescription
#N/AA 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: 

Please note that three similar functions can be used for error checking. 

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!")

Exit mobile version