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.                              