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:

=NA()

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:

{=SUM(IF(ISERROR(Data),1))}

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:

=COUNTIF(Data,”#DIV/0!”)

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

arguments:

•
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