Microsoft Office Tutorials and References
In Depth Information
Excel has three functions that help you determine whether a cell contains an error value:
• ISERROR: Returns TRUE if the cell contains any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!,
#NAME?, or #NULL!)
• ISERR: Returns TRUE if the cell contains any error value except #N/A
• ISNA: Returns TRUE if the cell contains the #N/A error value
Notice that the #N/A error value is treated separately. In most cases, #N/A is not a “real”
error. #N/A is often used as a placeholder for missing data. You can enter the #N/A error
value directly or use the NA function:
You can use these functions in an array formula to count the number of error values in a range. The following
array formula, for example, returns the total number of error values in a range named Data:
Depending on your needs, you can use the ISERR or ISNA function in place of ISERROR.
If you need to count specific types of errors, you can use the COUNTIF function. The following formula, for
example, returns the number of #DIV/0! error values in the range named Data:
Note that the COUNTIF functions works only with a contiguous range argument. If Data is defined a noncon-
tiguous range, the formula returns a #VALUE! error.
Advanced Counting Formulas
Most of the basic examples I presented previously use functions or formulas that perform conditional counting.
The advanced counting formulas that I present here represent more complex examples for counting worksheet
cells, based on various types of selection criteria.
Counting cells with the COUNTIF function
Excel's COUNTIF function is useful for single-criterion counting formulas. The COUNTIF function takes two
• range: The range that contains the values that determine whether to include a particular cell in the count
• criteria: The logical criteria that determine whether to include a particular cell in the count