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.

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.

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

Start Free Trial

No credit card required