Count the Errors in a Range in Excel

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.

Result:

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.

Result:

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.