Microsoft Excel - List Functions

 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	✅	✅