Working...
Introduction to Excel’s Database Functions
Excel's Database Functions work with data ranges treated like a database table.
They use field names (headers) and criteria ranges to calculate results based on conditions.
Common structure:
Function(Database, Field, Criteria)
- Database → Entire data range including headers
- Field → Column to calculate on (name or index)
- Criteria → Range showing condition(s)
✅ DSUM() — Sum Based on Criteria
Purpose: Sum values in a column that match specific condition(s).
Syntax
=DSUM(database, field, criteria)
Example
Sum Sales where Region = "East"
Criteria setup:
| Region | | East |
Formula:
=DSUM(A1:C100, "Sales", E1:E2)
Excel DSUM Function — Single Criteria (Continued)
You can use any column as a filter — e.g., Product = "Laptop".
| Product | | Laptop |
=DSUM(A1:C100, "Sales", E1:E2)
DSUM with OR Criteria
Place criteria in separate rows.
| Region |
| East |
| West |
Result = Sum where Region is East OR West
=DSUM(A1:C100, "Sales", E1:E3)
DSUM with AND Criteria
Place criteria in the same row.
| Region | Product | | East | Laptop |
Result = Sum where Region = East AND Product = Laptop
=DSUM(A1:C100, "Sales", E1:F2)
✅ DAVERAGE() — Average Based on Criteria
Purpose: Returns average values filtered by criteria.
Syntax
=DAVERAGE(database, field, criteria)
Example: Average Salary where Department = HR.
✅ DCOUNT() — Count Numeric Records Based on Criteria
Purpose: Count numeric cells matching criteria.
Syntax
=DCOUNT(database, field, criteria)
Use when counting numbers only.
✅ SUBTOTAL() — Dynamic Summary Function
Purpose: Performs operations like sum, average, count — but ignores hidden or filtered rows.
Syntax
=SUBTOTAL(function_num, range)
Common function numbers
| Function | No. |
|---|---|
| AVERAGE | 1 |
| COUNT | 2 |
| SUM | 9 |
| MAX | 4 |
| MIN | 5 |
Example
=SUBTOTAL(9, B2:B100)
(9 = SUM)
Key Difference: DSUM vs SUBTOTAL
| Feature | DSUM | SUBTOTAL |
|---|---|---|
| Criteria based? | ✅ Yes | ❌ No (uses filters) |
| Works like database query | ✅ | ❌ |
| Good for filtered totals | ✅ | ✅ |
