Syntax. DCOUNT(database,field,criteria)
Definition. This function counts the number of records containing numbers in the field column that match the specified conditions.
Arguments
database (required) The cell range that identifies the list or database.
field (optional) Indicates which column is used in the function. Enter the column label enclosed in quotation marks, such as “country/region”, “sales”, or “company”. You can also enter a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
criteria (required) The cell range containing the field names and the filter criteria. You can use any range for the criteria argument as long as the argument includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
Background. The DCOUNT() function performs a simple task but can save a lot of time, especially when you work with databases and have to count many records. To count records containing numbers that match certain criteria in a database, use the DCOUNT() function.
Note
The DCOUNT() function counts all values that are numbers, 0, dates, or a text representation of numbers. Values that are error values or text that cannot be converted into numbers are not counted.
Example. You are a wholesaler and want to know how many deliveries have been invoiced. This means that you want to return all sales greater than 0.
Open a new worksheet and define the criteria range as the Sales field from the original data. Then specify the search criterion >0.
Now use the DCOUNT() function to calculate the number of sales that are more than zero in the database (see Figure 13-7).
Specify the named range Database for the database argument, this is A1:F7008. In this example, the cell range has the dynamic name Database so that you can avoid having to type in the cell range A1:F7008 each time you enter the database range.
Note
Dynamic names are explained in detail in the section titled Dynamic Database Names in Chapter 2.
As you can see in Figure 13-8, you 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 13-8, the result is still 7,000.
This means that the database contains 7,000 records for sales greater than zero. In the same way, you can quickly count the sales greater than 1,500.