Microsoft Office Tutorials and References
In Depth Information
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
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).
Counting blank cells
The following formula returns the number of blank (empty) cells in a range named 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.
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:
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.