Page 297 - Excel 2007 Bible

19_044039 ch14.qxp 11/21/06 11:06 AM Page 254
Part II
Working with Formulas and Functions
You can use these functions in an array formula to count the number of error values in a range. The follow-
ing 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 for-
mula, 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 earlier in this chapter use functions or formulas that perform condi-
tional counting. The advanced counting formulas that I present here represent more complex examples for
counting worksheet cells, based on various types of criteria.
Some of these examples are array formulas. Refer to Chapters 17 and 18 for more information
CROSS-REF
CROSS-REF
about array formulas.
Counting cells by using the COUNTIF function
Excel’s COUNTIF function is useful for single-criterion counting formulas. The COUNTIF function takes two
arguments:
n range: The range that contains the values that determine whether to include a particular cell in
the count
n criteria: The logical criteria that determine whether to include a particular cell in the count
Table 14.2 lists several examples of formulas that use the COUNTIF function . These formulas all work with
a range named Data. As you can see, the criteria argument proves quite flexible. You can use constants,
expressions, functions, cell references, and even wildcard characters (* and ?).
TABLE 14.2
Examples of Formulas Using the COUNTIF Function
=COUNTIF(Data,12) Returns the number of cells containing the value 12
=COUNTIF(Data,”<0”) Returns the number of cells containing a negative value
=COUNTIF(Data,”<>0”) Returns the number of cells not equal to 0
=COUNTIF(Data,”>5”) Returns the number of cells greater than 5
=COUNTIF(Data,A1) Returns the number of cells equal to the contents of cell A1
=COUNTIF(Data,”>”&A1) Returns the number of cells greater than the value in cell A1
=COUNTIF(Data,”*”) Returns the number of cells containing text
=COUNTIF(Data,”???”) Returns the number of text cells containing exactly three characters
=COUNTIF(Data,”budget”) Returns the number of cells containing the single word budget (not case
sensitive)

254