How to Use the DCOUNT and DCOUNTA Functions in Excel

By Stephen L. Nelson, E. C. Nelson

The DCOUNT and DCOUNTA functions in Excel count records in a database table that match criteria that you specify. Both functions use the same syntax, as you can see here:

=DCOUNT(database,field,criteria)
=DCOUNTA(database,field,criteria)

where database is a range reference to the Excel table that holds the value that you want to count, field tells Excel which column in the database to count, and criteria is a range reference that identifies the fields and values used to define your selection criteria.

The field argument can be a cell reference holding the field name, the field name enclosed in quotation marks, or a number that identifies the column (1 for the first column, 2 for the second column, and so on).

Excel provides several other functions for counting cells with values or labels: COUNT, COUNTA, COUNTIF, and COUNTBLANK.

The functions differ subtly, however. DCOUNT counts fields with values; DCOUNTA counts fields that aren’t empty.

As an example of how the DCOUNT and DCOUNTA functions work, suppose that you’ve constructed this worksheet, which contains a list of players on a softball team. Row 1 stores field names: Player, Age, and Batting Average. Rows 2–11 store individual records.

image0.jpg

Rows 14 and 15 store the criteria range. Field names go into the first row. Subsequent rows provide labels or values or Boolean logic expressions that the DCOUNT and DCOUNTA functions use to select records from the list for counting.

For example, there’s a Boolean expression in cell B15, which tells the function to include only records where the Age shows a value greater than eight. In this case, then, the functions count players on the team who are older than 8.

The DCOUNT function, which appears in cell F3, is

=DCOUNT(A1:C11,C1,A14:C15)

The function counts the players on the team who are older than 8. But because the DCOUNT function looks only at players with a batting average in the Batting Average field, it returns 8. Another way to say this same thing is that in this example, DCOUNT counts the number of players on the team who are older than 8 and have a batting average.

The DCOUNTA function, which appears in cell F5, is

=DCOUNTA(A1:C11,3,A14:C15)

The function counts the players on the team who are older than 8 and have some piece of information entered into the Batting Average field. The function returns the value because each of the players older than 8 have something stored in the Batting Average field. Eight of them, in fact, have batting average values.

If you just want to count records in a list, you can omit the field argument from the DCOUNT and DCOUNTA functions. When you do this, the function just counts the records in the list that match your criteria without regard to whether some field stores a value or is nonblank.

=DCOUNT(A1:C11,,A14:C15)
=DCOUNTA(A1:C11,,A14:C15)

Note: To omit an argument, you just leave the space between the two commas empty.