Lesson 201: How to count errors in Excel

This article will show you how to create an array formula to count errors in Excel.

How to count errors in Excel

1. We use IF and ISERROR to check for errors.

Explanation: the IF function returns 1, if an error is found. Otherwise, it returns an empty string.

2. To count errors (without overloading), we add the COUNT function and replace A1 with A1:C3.

3. Finish by pressing CTRL + SHIFT + ENTER.

Note: The formula bar indicates this is an array formula because it is enclosed in curly braces {}. Don’t type these yourself. They will disappear when you edit the formula.

Explanation: The range (array constant) created by the IF function is stored in Excel’s memory, not in a range. The array constant looks like this:

{1,””,1;””,””,””;””,””,1}

This array constant is used as a parameter to the COUNT function, which returns 3.

4. To count specific errors, use the COUNTIF function. For example, count the number of cells that contain the #DIV/0! error.

In addition, you can also view many other excel articles here.