Microsoft Office Tutorials and References

In Depth Information

**Advanced Counting Formulas**

Counting logical values

The following array formula returns the number of logical values (TRUE or FALSE) in a range

named
Data:

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

Counting error values in a range

Excel has three functions that help you determine whether a cell contains an error value:

h
ISERROR:
Returns TRUE if the cell contains any error value (#N/A, #VALUE!, #REF!,

#DIV/0!, #NUM!, #NAME?, or #NULL!)

h
ISERR:
Returns TRUE if the cell contains any error value except #N/A

h
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 would like 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!”)

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.