Microsoft Office Tutorials and References
In Depth Information
Counting nonblank cells
=IF(A1>5,"",A1)
You can use the COUNTBLANK function with an argument that consists of entire rows or
columns. For example, the following 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)
Counting nonblank cells
To count nonblank cells, use the COUNTA function. The following formula uses the COUNTA
function to return the number of nonblank cells in a range named Data:
17
=COUNTA(Data)
The COUNTA function counts cells that contain values, text, or logical values ( TRUE or
FALSE ).
If a cell contains a formula that returns an empty string, that cell is included in the count returned by COUNTA , even
though the cell appears to be blank.
Counting numeric cells
To count only the numeric cells in a range, use the following formula (which assumes the
range is named Data):
=COUNT(Data)
Cells that contain a date or a time are considered to be numeric cells. Cells that contain a
logical value ( TRUE or FALSE ) aren’t considered to be numeric cells.
Counting text cells
To count the number of text cells in a range, you need to use an array formula. The array
formula that follows returns the number of text cells in a range named Data:
{=SUM(IF(ISTEXT(Data),1))}
Search JabSto ::




Custom Search