Microsoft Office Tutorials and References

In Depth Information

**Basic Counting Formulas**

You can access the
basic counting.xlsx
workbook shown in Figure 7-1 on the

companion CD-ROM.

Counting the total number of cells

To get a count of the total number of cells in a range, use the following formula. This formula

returns the number of cells in a range named
Data.
It simply multiplies the number of rows

(returned by the ROWS function) by the number of columns (returned by the COLUMNS function).

=ROWS(Data)*COLUMNS(Data)

Counting blank cells

The following formula returns the number of blank (empty) cells in a range named
Data:

=COUNTBLANK(Data)

The COUNTBLANK function also counts cells containing a formula that returns an empty string.

For example, the formula that follows returns an empty string if the value in cell A1 is greater

than 5. If the cell meets this condition, the COUNTBLANK function counts that cell.

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

The COUNTBLANK function does not count cells that contain a zero value, even if you

clear the Show a Zero in Cells That Have Zero Value option in the Excel Options dialog

box. (Choose File

Options and navigate to the Display Options for this Worksheet

section of the Advanced tab.)

➜

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

columns. For example, this next 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)