Microsoft Office Tutorials and References
In Depth Information
Basic Counting Formulas
You can access the basic counting.xlsx workbook shown in Figure 7-1 on the
companion CD-ROM.
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 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)
Counting blank 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 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
section of the Advanced tab.)
You can use the COUNTBLANK function with an argument that consists of entire rows or
columns. For example, 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)
 
Search JabSto ::




Custom Search