Page 155 - Excel Workbook for Dummies

15_798452 ch10.qxp 3/13/06 7:33 PM Page 138
138 Part II: Using Formulas and Functions
In addition to these standard counting functions, the program includes a COUNTIF
function that works much like the SUMIF function you encountered in Chapter 9.
You can use this function to return the count in a cell range of only those cells whose
entries meet the condition you set up in its criteria argument. This function uses the
following syntax:

COUNTIF(range,criteria)
When specifying a number for the criteria argument of the COUNTIF function, you
simply enter the number or the reference to the cell that contains the number. When
specifying a comparative expression or text for the criteria argument, you must
remember to enclose the argument in a set of double quotation marks. For example,
to use COUNTIF to find the number of cells in the range E15:E45 that contain the
number 50, you would enter the following formula:

=COUNTIF(E15:E45,50)

If, however, you want to know the number of cells in this range that contain values
greater than or equal to 50, you would enter this formula:

=COUNTIF(E15:E45,”>=50”)
Further, suppose that cell D10 contains the numeric entry 50 and you want to con-
struct the COUNTIF formula using this cell reference in the criteria argument rather
than the number itself. You would have to enter this version of the formula as

=COUNTIF(E15:E45,”>=”&D10)
Remember from Table 5-1 that the & (ampersand) acts as the concatenation text
operator that connects text to another entry (in this case, it connects the text “>=” to
the cell reference, D10) to produce one continuous entry.

If you enclose a cell reference (such as D10 in the previous example) inside quotation
marks in the COUNTIF criteria argument, Excel interprets the cell address as a text
string to locate in the entries in the function’s range argument.

Try It

Exercise 10-3: Building Formulas with the COUNT, COUNTBLANK, COUNTA,
and COUNTIF Functions

Open the Exercise10-3.xls workbook file in your Chapter 10 folder inside the My
Practice Spreadsheets folder or in the Excel Workbook folder on the workbook
CD-ROM. This workbook contains a version of the Home Sales-06 worksheet that
you can use to practice using the counting functions:

1. Select the cell range B1:D8 in the Home Sales-06 worksheet and assign the range
name Sales_table to it.
2. Position the cell cursor in D12 and construct a formula using the COUNTA statis-
tical function with Sales_table as its argument that returns the number of cells
with entries of any kind in this range.
3. Position the cell cursor in D14 and construct a formula using the COUNT func-
tion with Sales_table as its argument that returns the number of cells with
numeric entries in this range.