Basic Counting Formulas

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.

Figure 17-1. Formulas in column E display various counts of the data in A1:B10.


Counting the total number of 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.

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)

About This Chapter’s Examples

Most of the examples in this chapter use named ranges for function arguments. When you adapt these formulas for your own use, you’ll need to substitute either the actual range address or a range name defined in your workbook.

Also, some examples consist of array formulas. An array formula is a special type of formula that enables you to perform calculations that would not otherwise be possible. You can spot an array formula because it’s enclosed in curly brackets when it’s displayed in the Formula bar. In addition, I use this syntax for the array formula examples presented in this book. 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 use 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).


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

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

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset