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

Similar Content on TeachExcel
Highlight Every Other Row in a Selection in Excel - Table Formatting
Macro: This free Excel macro will highlight every other row in a selection of cells with a co...
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a...
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...
Sort Data With Headers in Descending Order in Excel
Macro: This Excel macro sorts data that has headers in descending order. This means that dat...