Microsoft Office Tutorials and References

In Depth Information

**Counting nonblank cells**

=IF(A1>5,"",A1)

You can use the
COUNTBLANK
function with an argument that consists of entire rows or

columns. For example, the following formula returns the number of blank cells in

column A:

=COUNTBLANK(A:A)

The following formula returns the number of empty cells on the entire worksheet named

Sheet1. You must enter this formula on a sheet other than Sheet1, or it will create a

circular reference.

=COUNTBLANK(Sheet1!1:1048576)

Counting nonblank cells

To count nonblank cells, use the
COUNTA
function. The following formula uses the
COUNTA

function to return the number of nonblank cells in a range named Data:

17

=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 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
) arenâ€™t 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))}