Count cells that contain errors

★ 29 minutes 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




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




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.


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