How to Use the DCOUNT Function in an Excel Database - dummies

How to Use the DCOUNT Function in an Excel Database

By Ken Bluttman

The DCOUNT function in Excel lets you determine how many records in the database match the criteria. The figure 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:

Calculating the number of students in each course.
Calculating the number of students in each course.
=DCOUNT(Students,"Final Grade",F8:G9)

Note that DCOUNT requires a column of numbers to count. Therefore, the Final Grade heading is put in the function. Counting on Class or Teacher would result in zero. Using a column that specifically has numbers may seem a little odd. The function is not summing the numbers; it just counts the number of records. But what the heck? It works.

Now take this a step further. How about counting the number of students who got a grade of 90 or better in any class? How can this be done? This calculation requires a different criterion — one that selects all records where Final Grade is 90 or greater. The following figure shows a worksheet with this criterion and the calculated result shown.

Calculating the number of students who earned a grade of 90 or better.
Calculating the number of students who earned a grade of 90 or better.

The result in cell F6 concatenates — that is, combines but does not add — the answer from the DCOUNT function with some text. The formula looks like this:

=DCOUNT(Students,"Final Grade",F2:F3) & " students received a 90 or better."

The criterion specifically states to use all records where the Final Grade is greater than 89 (>89). You can specify >=90 with the exact same result.