Microsoft Office Tutorials and References

In Depth Information

**Counting the total number of cells**

About This Chapter’s Examples

Most of the examples in this chapter use named ranges for function arguments. When you adapt these

formulas for your own use, you’fill need to substitute either the actual range address or a range name

deﬁ ned in your workbook. (See Chapter 14 for information about using named ranges.)

Also, some examples consist of array formulas. An
array formula
is a special type of formula that enables

you to perform calculations that would not otherwise be possible. You can spot an array formula because

it’s enclosed in curly brackets when it’s displayed in the Formula bar. In addition, I use this syntax for

the array formula examples presented in this topic. For example:

{=Data*2}

When you enter an array formula, press Ctrl+Shift+Enter (not just Enter), but
don’t
type the curly

brackets (Excel inserts the brackets for you). If you need to edit an array formula, don’t forget to press

Ctrl+Shift+Enter when you ﬁ nish editing; otherwise, the array formula will revert to a normal formula,

and it will return an incorrect result. (See the Help topic “Guidelines and examples of array formulas”

for an introduction to array formulas.)

Counting the total number of cells

Oddly, Excel doesn’t have a function that simply counts the number of cells in a range

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

This function works only with a contiguous range of cells. If Data is deﬁ ned as a

noncontiguous range, the function returns a
#VALUE!
error.

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.