Microsoft Office Tutorials and References

In Depth Information

Counting nonblank cells

The following formula uses the COUNTA function to return the number of nonblank cells in a range named

Data:

=COUNTA(Data)

The COUNTA function counts cells that contain values, text, or logical values (TRUE or FALSE).

If a cell contains a formula that returns an empty string, that cell is included in the

count returned by COUNTA even though the cell appears to be blank.

Counting numeric cells

To count only the numeric cells in a range, use the following formula, which assumes that the range is named

Data:

=COUNT(Data)

Cells that contain a date or a time are considered to be numeric cells. Cells that contain a logical value (TRUE

or FALSE) are not considered to be numeric cells.

Counting text cells

To count the number of text cells in a range, you need to use an array formula. The array formula that follows

returns the number of text cells in a range named
Data:

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

Counting nontext cells

The following array formula uses Excel's 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