Office 2007 Bible

Basic Counting Formulas

The basic counting formulas presented here are all straightforward and relatively simple. They demonstrate the capability of the Excel counting functions to count the number of cells in a range that meet specific criteria. Figure 17-1 shows a worksheet that uses formulas (in column E) to summarize the contents of range A1:B10—a 20-cell range named Data. This range contains a variety of information, including values, text, logical values, errors, and empty cells.

Figure 17-1. Formulas in column E display various counts of the data in A1:B10.

Counting the total number of cells

To get a count of the total number of cells in a range (empty and nonempty cells), 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)

This formula will not work if the Data range consists of noncontiguous cells. In other words, Data must be a rectangular range of cells.

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 ...