Basic Counting Formulas | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

The basic counting formulas presented here are all straightforward and relatively simple. They demonstrate how to count the number of cells in a range that meet specific criteria. Figure 7-1 shows a worksheet that uses formulas (in column E) to summarize the contents of range A1:B10-a 20-cell range named Data.

image from book
Figure 7-1: Formulas provide various counts of the data in A1:B10.

On the CD

You can access the basic counting.xlsx workbook shown in Figure 7-1 on the companion CD-ROM.

About This Chapter's Examples

Some of the examples in this chapter use array formulas. An array formula, as explained in Chapter 14, is a special type of formula. You can spot an array formula because it is enclosed in brackets when it is displayed in the formula bar. For example

 {=Data*2}

When you enter an array formula, press Ctrl+Shift+Enter (not just Enter). And don't type the brackets-Excel inserts the brackets for you. If you need to edit an array formula, don't forget to press Ctrl+Shift+Enter when you've finished editing. Otherwise, the array formula will revert to a normal formula, and it will return an incorrect result.

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)

Note

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 Office Excel 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)

Counting Nonblank Cells

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.

Counting Numeric Cells

To count only the numeric cells in a range, use the following formula, which assumes that 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) are not considered to be numeric cells.

Counting Nontext Cells

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

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 Logical Values

The following array formula returns the number of logical values (TRUE or FALSE) in a range named Data:

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

Counting Error Values in a Range

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

Note

Notice that the #N/A error value is treated separately. In most cases, #N/A is not a "real" error. #N/A is often used as a placeholder for missing data. You can enter the #N/A error value directly or use the NA function:

 =NA()

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!")