Microsoft Office Tutorials and References

In Depth Information

{=Data*2}

When you enter an array formula, press Ctrl+Shift+Enter (not just Enter). You don't need to type the brackets —

Excel inserts the brackets for you. And if you need to edit an array formula, don't forget to press Ctrl+Shift+Enter

after you finish editing. Otherwise, the array formula will revert to a normal formula, and it will return an incorrect

result.

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

ber 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)

This function works only with a contiguous range of cells. If
Data
is defined 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.

=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 sec-

tion of the Advanced tab.)

You can use the COUNTBLANK function with an argument that consists of entire rows or columns. For ex-

ample, 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)