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

## Leave a Comment