Learn how to use the DCOUNT function to sum the values in a column of data that meet a set of criteria.
These instructions apply to Excel 2019, 2016, 2013, 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Microsoft 365, and Excel Online.
The DCOUNT function is one of Excel's database functions. This set of functions is designed to make it easy to summarize information from large data tables. They do this by returning specific information based on one or more criteria selected by the user.
The syntax of the DCOUNT function is:
=DCOUNT(database,field,condition)
All database functions have the same three parameters:
- Database : (Required) Specifies the cell reference range that contains the database. Field names must be included in the range.
- Field : (Required) Indicates which column or field the function uses in its calculation. Enter the parameter by typing the field name in quotation marks (for example, "radius") or entering the column number (for example, 3).
- Criteria : (Required) Lists the range of cells that contain user-specified criteria. The range must contain at least one field name from the database and at least one other cell reference that indicates the condition to be evaluated by the function.
This example uses DCOUNT to find the total number of students enrolled in the first year of a college course.
This tutorial does not include formatting steps. Information about worksheet formatting options can be found in this basic Excel formatting tutorial.
Enter the data table as shown below into cells D1 to F15.
Leave cell F5 blank. This is where the DCOUNT formula lives. Cell E5 is titled Total: Represents the information we will find via DCOUNT.
The field names in cells D2 through F2 will be used as part of the function's Criteria parameters.
To have DCOUNT view data only for first-year students, enter the number 1 under the Year field name on row 3.
Using named ranges for large ranges of data, such as databases, not only makes it easier to enter parameters into functions, but it also prevents errors caused by choosing the wrong range.
Named ranges are useful if you frequently use the same range of cells when calculating or creating charts or graphs.
Highlight cells D6 to F15 in the worksheet to select the range.
Click the name box above column A in the worksheet.
Type Enrollment in the Name box to create a named scope.
Press the Enter key on the keyboard to complete the input.
A function's dialog box provides an easy way to enter data for each parameter of the function.
You can open the dialog box for the Database Function group by clicking the Function Wizard button (fx) next to the formula bar above the worksheet.
Click cell F5 , which is where the function result will be displayed.
Click the effects button .
Type DCOUNT in the Search Function window at the top of the dialog box.
Click the "Go" button to search for the function. The dialog box should find DCOUNT and list it in the Select Function window.
Click OK to open the DCOUNT function dialog box.
Click the database row of the dialog box.
Enter the range name Enrollment on the line.
Click the field row of the dialog box.
Enter the field name "Year" in the row. Be sure to include quotation marks.
Click the "Conditions" row of the dialog box.
Highlight cells D2 to F3 in the worksheet to enter the range.
Click OK to close the DCOUNT function dialog box and complete the function.
