DCOUNT Function in Excel
Purpose:DCOUNT counts the number of numeric values in a column (field) of a database that meet specific criteria.
Syntax:
DCOUNT(database, field, criteria)- database → Full data range (including headers)
- field → Column name (in quotes) or column index number
- criteria → Criteria range (with header)
Sample Real-World Dataset (Employee Sales Data)
Use this dataset in Excel (Range: A1:F16)
| EmpID | Name | Department | City | Salary | Sales |
|---|---|---|---|---|---|
| 101 | Amit | Sales | Delhi | 45000 | 120 |
| 102 | Neha | HR | Mumbai | 38000 | 0 |
| 103 | Raj | Sales | Delhi | 52000 | 200 |
| 104 | Priya | IT | Pune | 60000 | 0 |
| 105 | Karan | Sales | Mumbai | 47000 | 150 |
| 106 | Simran | IT | Delhi | 75000 | 0 |
| 107 | Mohan | HR | Pune | 35000 | 0 |
| 108 | Pooja | Sales | Delhi | 49000 | 170 |
| 109 | Ravi | IT | Mumbai | 68000 | 0 |
| 110 | Anjali | Sales | Pune | 51000 | 190 |
| 111 | Arjun | HR | Delhi | 36000 | 0 |
| 112 | Meena | Sales | Mumbai | 53000 | 210 |
| 113 | Deepak | IT | Delhi | 72000 | 0 |
| 114 | Ritu | Sales | Pune | 46000 | 140 |
| 115 | Vikram | HR | Mumbai | 39000 | 0 |
BEGINNER LEVEL (1–7)
1 – Count employees in Sales department
Criteria Range:
| Department |
|---|
| Sales |
Formula:
=DCOUNT(A1:F16,"EmpID",H1:H2)2 – Count employees from Delhi
Criteria:
| City |
|---|
| Delhi |
3 – Count employees with Salary > 50000
Criteria:
| Salary |
|---|
| >50000 |
4 – Count employees in HR department
5 – Count employees from Mumbai
6 – Count employees with Sales > 150
7 – Count employees with Salary < 40000
INTERMEDIATE LEVEL (8–14)
8 – Count Sales department employees from Delhi
Criteria:
| Department | City |
|---|---|
| Sales | Delhi |
9 – Count IT employees with Salary > 65000
10 – Count employees from Pune with Salary > 45000
11 – Count Sales employees with Sales > 150
12 – Count employees where Salary between 40000 and 60000
Criteria:
| Salary | Salary |
|---|---|
| >=40000 | <=60000 |
13 – Count HR employees OR IT employees
Criteria:
| Department |
|---|
| HR |
| IT |
(OR logic works when criteria in separate rows)
15 – Count employees from Delhi OR Mumbai
ADVANCED LEVEL (15–20)
15 – Count Sales employees from Delhi with Salary > 45000
16 – Count employees with Sales > 100 AND Salary > 45000
17 – Count employees from Sales or IT with Salary > 60000
Criteria:
| Department | Salary |
|---|---|
| Sales | >60000 |
| IT | >60000 |
18 – Count employees whose Name starts with “A”
Criteria:
| Name |
|---|
| A* |
20 – Count employees NOT from Delhi
Criteria:
| City |
|---|
| <>Delhi |
20 – Dynamic DCOUNT using cell reference
Criteria:
| Department |
|---|
| =H2 |
Formula:
=DCOUNT(A1:F16,"EmpID",H1:H2)
(Change H2 value to Sales/HR/IT dynamically)
Important Truth About DCOUNT
- It only counts numeric values in the selected field.
- If you use
"Name"as field → It won’t count because it’s text. - If column contains blank cells → Those are ignored.
- Field must match header exactly.
Interview Trap
× Many people think DCOUNT works like COUNTIF.
✔ Reality: DCOUNT works only with structured database + criteria range.