This function counts the number of records that contain numerical values in the specified field column, provided they match the defined conditions.
Syntax
DCOUNT(database; field; criteria)
Arguments
- database (required): This is the cell range that identifies your list or database.
- field (optional): This indicates which column the function will use for counting. You can enter the column label enclosed in quotation marks (e.g., « country/region », « sales », or « company »). Alternatively, you can use a number representing the column’s position within the list (e.g., 1 for the first column, 2 for the second, and so on).
- criteria (required): This is the cell range that holds your field names and the filter criteria. Any range can be used for the criteria argument, as long as it includes at least one column label and at least one cell below that label where you specify a condition for the column.
Background
The DCOUNT() function performs a simple task, but it can be a huge time-saver, especially when you’re working with databases and need to count many records. Use the DCOUNT() function to count records that contain numbers and match specific criteria within a database.
Example
Imagine you’re a wholesaler and you want to know how many deliveries have been invoiced. This means you want to count all sales that are greater than zero.
First, open a new worksheet and define your criteria range using the « Sales » field from your original data. Then, specify the search criterion as >0.
Now, use the DCOUNT() function to calculate the number of sales in your database that are greater than zero (see Figure below).
For the database argument, specify the named range « Database », which refers to A1:F7008. In this example, using a dynamic name like « Database » saves you from having to type the cell range A1:F7008 every time you refer to your database.
As you can see in Figure below, you’ll get the same result if you enter the name « SEARCH1 » for the criteria range (A11:A12 in the figure) and specify « Sales » instead of cell A11 for the field argument. Remember to enclose the field name, « Sales », in quotation marks. As shown in Figure below, the result remains 7,000.
This means your database contains 7,000 records for sales greater than zero. You can quickly count sales greater than 1,500 in the same way.