Excel 2010 Bible

Chapter 13: Creating Formulas That Count and Sum

285

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)

Counting nonblank cells .........................................................................................

To count nonblank cells, use the COUNTA function. The following formula uses the COUNTA func-

tion to return the number of nonblank cells in a range named Data:

=COUNTA(Data)

The COUNTA function counts cells that contain values, text, or logical values (TRUE or FALSE).

Note
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. n

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

Counting nontext cells ............................................................................................

The following array formula uses the Excel ISNONTEXT function, which returns TRUE if its argu-

ment refers to any nontext cell (including a blank cell). This formula returns the count of the num-

ber of cells not containing text (including blank cells):

{=SUM(IF(ISNONTEXT(Data),1))}