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.

Sections:

Count the Number of Errors

Count Specific Errors

Notes

Count the Number of Errors

Count all of the errors that occur in a range.

=SUM(ISERROR(A1:A5)*1)

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.

cf17d7ced0b9e7ae5b803bc378869520.png

Result:

c0fd23b3a5aa116b05d62609f3398972.png

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.

=COUNTIF(A1:A5,"#NAME?")

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.

191494abaf058c0f6f1bc7afd0018481.png

Result:

60026560b0ca16024d2bca69c88d743d.png

Notes

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
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...
Tutorial Details
Excel Function: ISERROR(), SUM()
Downloadable Files: Excel File
Similar Content
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...
Excel Forum