Microsoft Office Tutorials and References
In Depth Information
Counting the total number of cells
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.
Search JabSto ::

Custom Search