Part 1: Introduction
Definition
The DCOUNT function in Excel is a database function that counts selected database entries based on multiple conditions. It’s part of a group of functions referred to as “database functions,” which are primarily used with database data.
Purpose
The DCOUNT the function counts the number of numeric cells within a range based on one or more specified criteria.
Syntax & Arguments
The syntax for the DCOUNT function is: =DCOUNT(database, field, criteria)
- Database: This is the range of cells that constitutes the list or database. It must include a header row.
- Field: The column in the database to which the function will be applied. It can be given as text (the header name in quotes) or as a number that represents the position of the column within the list.
- Criteria: This is the cell range containing the conditions you specify. To specify the state, it must include at least one header label and at least one cell below the header label.
Return Value
The DCOUNT function will return a count of selected database entries based on the conditions set.
Remarks
- The
DCOUNTthe part counts only numeric values. If you want to count cells containing text values based on criteria, you should useDCOUNTA. - The criteria range can be located anywhere on the worksheet but should not overlap the database.
Part 2: Examples
Example 1
Purpose of Example: To count the number of products with a price greater than 200.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Product | Price |
| 2 | Apples | 150 |
| 3 | Bananas | 250 |
| 4 | Oranges | 300 |
| 5 | ||
| 6 | Product | Price |
| 7 | >200 | |
| 8 | ||
| 9 | Formula | =DCOUNT(A1:B4, "Price", B6:B7) |
Result Table
| A | B | |
|---|---|---|
| 9 | Result | 2 |
Example 2
Purpose of Example: To count the number of orders with a quantity less than 500.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Order | Quantity |
| 2 | Order1 | 300 |
| 3 | Order2 | 500 |
| 4 | Order3 | 700 |
| 5 | ||
| 6 | Order | Quantity |
| 7 | <500 | |
| 8 | ||
| 9 | Formula | =DCOUNT(A1:B4, "Quantity", B6:B7) |
Result Table
| A | B | |
|---|---|---|
| 9 | Result | 1 |
Example 3
Purpose of Example: To count the number of employees with a salary greater than 2000.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Employee | Salary |
| 2 | Bob | 2000 |
| 3 | Alice | 3000 |
| 4 | Charlie | 1500 |
| 5 | ||
| 6 | Employee | Salary |
| 7 | >2000 | |
| 8 | ||
| 9 | Formula | =DCOUNT(A1:B4, "Salary", B6:B7) |
Result Table
| A | B | |
|---|---|---|
| 9 | Result | 1 |
Example 4 (Nested with IF function)
Purpose of Example: To count the number of products with a price greater than 200 and display “High price” if such products exist.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Formula |
| 2 | Apples | 150 | |
| 3 | Bananas | 250 | |
| 4 | Oranges | 300 | |
| 5 | |||
| 6 | Product | Price | |
| 7 | >200 | ||
| 8 | |||
| 9 | =IF(DCOUNT(A1:B4, "Price", B6:B7) > 0, "High price", "Low price") |
Result Table
| C | |
|---|---|
| 9 | High price |
Explanation: In this example, we’re using the DCOUNT function to count the number of products with a price greater than 200. The IF function then checks whether the count is greater than 0. If it is, it displays “High price”; otherwise, it says “Low price”.
Example 5 (Nested with SUM function)
Purpose of Example: To find the sum of the total number of products and the count of products with a price over 200.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Formula |
| 2 | Apples | 150 | |
| 3 | Bananas | 250 | |
| 4 | Oranges | 300 | |
| 5 | |||
| 6 | Product | Price | |
| 7 | >200 | ||
| 8 | |||
| 9 | =SUM(DCOUNT(A1:B4, "Price", A2:A4), DCOUNT(A1:B4, "Price", B6:B7)) |
Result Table
| C | |
|---|---|
| 9 | 5 |
Explanation: This example demonstrates the DCOUNT function used with the SUM function. The first DCOUNT function counts the total number of products, and the second DCOUNT function calculates the number of products with a price over 200. The SUM function then adds these two counts together.
Example 6 (Nested with VLOOKUP function)
Purpose of Example: To find the number of products with a price greater than a specified value and display their product name using VLOOKUP.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Formula |
| 2 | Apples | 150 | |
| 3 | Bananas | 250 | |
| 4 | Oranges | 300 | |
| 5 | |||
| 6 | Product | Price | |
| 7 | >200 | ||
| 8 | |||
| 9 | =VLOOKUP(DCOUNT(A1:B4, "Price", B6:B7), A1:B4, 1, FALSE) |
Result Table
| C | |
|---|---|
| 9 | Bananas |
Explanation: Here, we’re using the DCOUNT function to count the number of products with a price greater than 200. Then, VLOOKUP is used to find the corresponding product name in the original table (A1:B4) for this count value.
Example 7 (Nested with AVERAGE function)
Purpose of Example: To find the average price and the number of products with a price greater than this average.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Formula |
| 2 | Apples | 150 | |
| 3 | Bananas | 250 | |
| 4 | Oranges | 300 | |
| 5 | |||
| 6 | Product | Price | |
| 7 | >200 | ||
| 8 | |||
| 9 | =DCOUNT(A1:B4, "Price", B6:B7) > AVERAGE(B2:B4) |
Result Table
| C | |
|---|---|
| 9 | TRUE |
Explanation: In this example, we use the DCOUNT function to count the number of products with a price greater than 200. Then we compare this count to the average cost of all products (calculated using the AVERAGE function). If the count is higher, the formula returns TRUE.
Part 3: Tips and Tricks
- Make sure the criteria range does not overlap with your data range.
- When defining criteria, use column headers precisely as they appear in the data range.
- Different fields’ measures can be in other rows of the bars range.
- Cells with text and zero values are considered non-numeric and are not included in the count.
- Use wildcard characters
?and*in criteria.?It matches any single character and*matches any sequence of characters. - Use the DATE function or express dates in double quotes when using dates as criteria to avoid misinterpretation.