Microsoft Office Tutorials and References

In Depth Information

**Counting nontext cells**

Counting nontext cells

The following array formula uses the Excel
ISNONTEXT
function, which returns
TRUE
if its

argument refers to any nontext cell (including a blank cell). This formula returns the count

of the number of cells not containing text (including blank cells):

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

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:

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

■

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 want to count speciﬁ c 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

a noncontiguous range, the formula returns a
#VALUE!
error.

Advanced Counting Formulas

Most of the basic examples presented earlier in this chapter use functions or formulas that

perform conditional counting. The advanced counting formulas that I present in this

section represent more complex examples for counting worksheet cells, based on various types

of criteria.