Excel 2010 Bible

Part II: Working with Formulas and Functions

286

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:

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

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

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

l (^) ISNA: Returns TRUE if the cell contains the #N/A error value
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 follow-
ing 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 present earlier in this chapter 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 criteria.

Cross-Reference
Some of these examples are array formulas. See Chapters 16 and 17 for more information about array
formulas. n