< Go Back

Count cells that contain errors

★ 29 minute read

In this tutorial, you will learn how to count cells that contain errors. To be able to do this, you will get familiar with SUMPRODUCT and ISERROR function. 

Count cells that contain errors

Formula

=SUMPRODUCT(ISERROR(range)*1)

Explanation

The only parameter of the function ISERROR is Range. This is the range of values that you want to test

The formula will first test each value in a range. It then returns the result in an array of TRUE and FALSE elements. TRUE represents the cells with errors. Finally, it will count the TRUE elements in that array.

Example 1

Considering the example below. The formula in D6 is

=SUMPRODUCT(ISERROR(B4:B9)*1)

 

 

Figure 1 – Count cells that contain errors

 

The ISERROR(B4:B9) portion return an array: {TRUE,TRUE,FALSE,FALSE,TRUE,FALSE}
After multiplying by 1, it will return like this: {1,1,0,0,1,0}
Finally, SUMPRODUCT function returns the sum of the items in the above array because there is only one array is supplied.

Notes

You can also use SUM instead of SUMPRODUCT function. However, you need to enter as an array formula using CTRL + SHIFT + ENTER. The formula will look like {=SUM(ISERROR(range)*1)}
ISERROR function counts all errors. If you use ISERR function, it will exclude #N/A

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar