The basic counting formulas presented here are all straightforward and relatively simple. They demonstrate the capability of the Excel counting functions to count the number of cells in a range that meet specific criteria. Figure 17-1 shows a worksheet that uses formulas (in column E) to summarize the contents of range A1:B10—a 20-cell range named Data. This range contains a variety of information, including values, text, logical values, errors, and empty cells.
To get a count of the total number of cells in a range (empty and nonempty cells), 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)
This formula will not work if the Data range consists of noncontiguous cells. In other words, Data must be a rectangular range of 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)
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)
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:
=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.
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.
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))}
The following array formula uses Excel’s ISNONTEXT function, which returns TRUE if its argument refers to any nontext cell (including a blank cell). This formula returns the count of the number of cells not containing text (including blank cells):
{=SUM(IF(ISNONTEXT(Data),1))}
The following array formula returns the number of logical values (TRUE or FALSE) in a range named Data:
{=SUM(IF(ISLOGICAL(Data),1))}
Excel has three functions that help you determine whether a cell contains an error value:
ISERROR: Returns TRUE if the cell contains any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!)
ISERR: Returns TRUE if the cell contains any error value except #N/A
ISNA: Returns TRUE if the cell contains the #N/A error value
You can use these functions in an array formula to count the number of error values in a range. The following array formula, for example, returns the total number of error values in a range named Data:
{=SUM(IF(ISERROR(data),1))}
Depending on your needs, you can use the ISERR or ISNA function in place of ISERROR.
If you would like to count specific types of errors, you can use the COUNTIF function. The following formula, for example, returns the number of #DIV/0! error values in the range named Data:
=COUNTIF(Data,"#DIV/0!")