Excel Formulas - Count number of cells that contain errors

Count number of cells that contain errors

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)
ISERR(value)

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.
The ISERR funtion returns True if the value refers to any error value except #N/A.

Explanation

To count the number of cells that contain errors the ISERR function have used along with the SUMPRODUCT funtction.

Formula

=SUMPRODUCT(--ISERR(B5:B11))

How the formula works

In the above formula the ISERR funtion returns True or False and the -- signs convert it 0 and 1 and make an arrly like -

{1;0;0;1;0;0;1;} and the SUMPRODUCT adds the value of array and return result.

Count number of cells that contain errors using range name

Explanation

Count the number of cells that contain errors within a range of cells by assigned a range name.

Formula

=SUMPRODUCT(--ISERR(Sample_Text))

How the formula works

In the above formula the ISERR funtion returns True or False from the cell range B5:B11 named as Sample_Text, and the -- signs convert it 0 and 1 and make an arrly like -

{1;0;0;1;0;0;1;} and the SUMPRODUCT adds the value of array and return result.

Count number of cells does not contain errors

Explanation

Count the number of cells that does not contain errors within a range of cells.

Formula

=SUMPRODUCT(--NOT(ISERR(B5:B11)))

How the formula works

In the above formula the ISERR funtion returns True or False from the cell range B5:B11, and the NOT will reverse the True and FALSE.
Therefore the -- signs convert the True's and Fasse's into 1 and 0 and make an arrly like -

{0;1;1;0;1;1;0;} and the SUMPRODUCT adds the value of the array and return result.

Previous: Excel Formulas - Count number of cells which are not blank using COUNTA()
Next: Excel Formulas - Count number of cells with only numbers