Table of Contents

Count Errors

This example shows you how to create an array formula that counts the number of errors in a range.

1. We use IF and ISERROR to check for an error.

If and IsError Function

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

2. To count the errors (don’t be overwhelmed) we add the COUNT function and replace A1 with A1:C3.

Array Formula

3. Finish by pressing CTRL + SHIFT + ENTER.

Count Errors in Excel

Note: the formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula. In Excel 365 or Excel 2021 finish by simply pressing Enter. You won’t see curly braces.

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

{1″”1;””””””;””””1}

This array constant is used as an argument for the COUNT function giving a result of 3.

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

Count #DIV/0! Errors

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog