Microsoft Office Tutorials and References

In Depth Information

**Counting Only What Matters with DCOUNT**

Figure 17-9:

Calculating

the

average grade

for each

course.

For the sake of comparison, DAVERAGE is also used in cell G24 to show the

overall average for all courses. Because a criterion is a required function

argument, the calculation in cell G24 is set to look at an empty cell. None of

the Class criteria cells are free, so the function looks to the Teacher criterion

in cell G3. Because this cell has no particular teacher entered as a criterion,

all of the records in the database are used to create this average — just what

you want. Here is the formula in cell G24:

=DAVERAGE(Students,”Final Grade”,G2:G3)

It doesn’t matter which field header you use in the criterion when getting a

result based on all records in a database. What
does
matter is that there is no

actual criterion underneath the header.

Counting Only What Matters

with DCOUNT

The DCOUNT function lets you determine how many records in the database

match the criteria.

Figure 17-10 shows how DCOUNT can determine how many students took

each course. Cells G18:G22 contain formulas that count records based on the

criterion (the Class) in the associated criteria sections. Here is the formula

used in cell G20, which counts the number of students in Calculus 101:

=DCOUNT(Students,”Final Grade”,F8:G9)