Counting Cells with Excel 2007’s COUNT Functions
Excel 2007 offers an assortment of counting functions — including COUNT, COUNTA, COUNTBLANK, and COUNTIF — in the Statistical category that enable you to count the number of cells that contain numeric values, that are nonblank (and thus contain entries of any kind), or whose values meet the criteria that you specify. You can find these functions on the Statistical continuation menu accessed from the More Functions button’s drop-down menu on the Formulas tab.
Building counting formulas
The following figure illustrates the different types of counting formulas that you can build to return such basic statistics as the total number of cells in a particular range, the number of occupied cells in that range, as well as the number of numeric and text entries in the occupied range. In this worksheet, the cell range A1:C7 has been assigned the name sales_data.
The most basic formula is the one that returns the total number of cells in the sales_data range. To build this formula in cell C10, use the ROWS and COLUMNS information functions to return the number of rows and columns in the range, and then create the following formula that multiplies these two values together:
This formula, of course, returns 21 to cell C9. The next formula calculates the number of these 21 cells that contain data entries (of whatever type) by using the COUNTA function. This function counts the number of cells that are not empty in the ranges that you specify. The COUNTA function uses the following syntax:
The value arguments (all of which are optional except for value1) are up to 30 different values or cell ranges that you want counted. In the example shown in the figure, cell C11 contains the following COUNTA function:
This formula returns 19 to cell C11. The next formula in the sample worksheet calculates the number of numeric entries in the cell range sales_data. To do this, you use the COUNT function. The COUNT function takes the same arguments as COUNTA, the only difference being that COUNT counts a value or cell specified in its value arguments only if it contains a numeric entry.
Cell C13 contains the following formula for calculating the number of numeric entries in the Home Sales table range called sales_data:
Excel returns 10 to cell C13. Note that in calculating this result, Excel counts the five date entries (with the date of each sale) in the cell range B3:B7 as well as the five numeric data entries (with the selling prices of each home) in the cell range C3:C7.
The next formula in the sample worksheet shown in the figure uses the COUNTBLANK function to calculate the number of blank cells in the sales_data range. The COUNTBLANK function works just like the COUNTA and COUNT functions except that it returns the number of nonoccupied cells in the range. This example uses the following COUNTBLANK function in cell C15:
Excel then returns 2 to cell C16.
Excel includes a COUNTIF function that you can use to count cells in a range only when they meet a certain condition. The COUNTIF function takes two arguments and uses the following syntax:
The range argument specifies the range of cells from which the conditional count is to be calculated. The criteria argument specifies the condition to use. You can express this argument as a number, expression, or text that indicates which cells to count. When specifying a number for the criteria argument, you don’t have to enclose the number in quotes. For example, in a cell range named table_data, to count the number of entries that contain the number 5, you enter the following COUNTIF formula:
However, when specifying an expression or text as the criteria argument, you must enclose the expression or text in closed quotes as in “=5“, “>20“, or “New York“. So, if you want to use COUNTIF to find out how many cells in the table_data range have values greater than 5, you enter this version of the COUNTIF function:
When you want to use the COUNTIF function to find out the number of cells whose contents are equal to the contents of a particular cell in the worksheet, you just add the cell reference as the function’s criteria argument. For example, if you want to count the number of cells in the table_data range that are equal to the contents of cell B3 in the worksheet, you enter this formula: