Count the Errors in a Range in Excel

Add to Favorites

How to count the number of errors in a range in Excel; also, how to count the occurrence of a specific error.


Count the Number of Errors

Count Specific Errors


Count the Number of Errors

Count all of the errors that occur in a range.


Array formula: you must enter this into the cell using Ctrl + Shift + Enter or it won't work.

A1:A5 change this to your range of data. That's all you have to do to get this to work for your data.




Since this is an array formula, you will notice in the last image the curly braces that appear around the formula in the Formula Bar. That means that the array formula was correctly entered; however it does not mean that the formula itself is correct.

Count Specific Errors

Count the number of times a specific error appears in a range.


This is NOT an array formula.

A1:A5 is the range to check; change this to fit your data.

#NAME? is the error that you want to count. Change this to the desired error and make sure it is surrounded with quotation marks.





Remember that the first example above is an array formula and the second is just a regular formula. Array formulas must be input using Ctrl + Shift + Enter.

Download the sample file for this tutorial to work with these examples in Excel.

Excel Function: ISERROR(), SUM()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Count the Number of Cells that Contain Specific Text in Excel
Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....
Find the Last Column with Data in Excel VBA
Tutorial: How to find the last column in a row that has data. This includes selecting that column or...
Create a Column Chart with a Macro in Excel
Macro: This macro adds a column chart to Excel. This is an easy to use macro that allows you to q...
Sort Data With Headers in Ascending Order in Excel
Macro: Macro that sorts data that has headers in ascending order in Excel. This macro assumes tha...
Sort Data that Doesn't Have Headers in Ascending Order in Excel
Macro: Sort data that doesn't have headers in ascending order in Excel with this macro. This is a...