Excel Database Functions — DAVERAGE, DMAX, DMIN & Mastering the Criteria Range

TL;DR — Excel's database functions all share one signature — Dxxx(database, field, criteria) — and one skill: the criteria range. Swap the aggregation and you swap the function: DAVERAGE for the mean of the matching rows, DMAX/DMIN for the biggest/smallest, DPRODUCT, DSTDEV, DVAR for the rest. Master the criteria block once and all twelve fall together. The rules that matter: AND is across a row, OR is down rows; a range on one field (100–500) needs that header twice; formula criteria need a header that's blank or different from the data's; and text matches from the start by default (North also catches Northwest). Get the criteria block right and the D-functions become the most auditable way to ask a complex, multi-condition question. Works in every Excel version.

=DAVERAGE(A1:E200, "Amount", H1:I2)   ' average Amount of the matching rows
=DMAX(A1:E200, "Amount", H1:H2)       ' largest matching Amount
=DMIN(A1:E200, "Amount", H1:H2)       ' smallest matching Amount

Individually, DSUM and DGET look like quirky one-offs. Seen together, the database functions are a family built on a single idea — you write your query as a table on the sheet — and that idea has depths most tutorials never reach. This guide is the criteria-range masterclass: get this one block right and every D-function, from DAVERAGE to DVAR, works the same way.

What you'll learn

• The full family: one signature, twelve aggregations
• AND vs OR in a criteria block — the rule that drives everything
• Why a range on one field (≥100 and ≤500) needs the header twice
• Formula criteria — the most powerful feature, and its two strict rules
• The starts-with text trap that silently over-counts
• The decision tree: D-functions vs SUMIFS/AVERAGEIFS vs FILTER

One signature, twelve aggregations

Every database function reads the same three arguments — a table (with headers), a field, and a criteria range — and differs only in how it aggregates the matching rows.

DAVERAGE is the honest one to remember: it averages only the numeric cells in the matching rows, so blanks don't drag the mean toward zero the way SUM/COUNT done by hand can. Learn the criteria range below and you can drive any row in this table.

AND across, OR down — the rule behind everything

The criteria range is a small table: a header row, then condition rows. The geometry is the logic.

' AND — same row: West region AND Amount over 1000
'   H1: Region    I1: Amount
'   H2: West      I2: >1000
=DAVERAGE(A1:E200, "Amount", H1:I2)

' OR — separate rows: West OR East
'   H1: Region
'   H2: West
'   H3: East
=DAVERAGE(A1:E200, "Amount", H1:H3)

' AND + OR together: (West AND >1000) OR (East AND >2000)
'   H1: Region    I1: Amount
'   H2: West      I2: >1000
'   H3: East      I3: >2000
=DAVERAGE(A1:E200, "Amount", H1:I3)

That last block is where D-functions earn their keep: expressing (West and over 1000) or (East and over 2000) in SUMIFS means nesting arrays; here it's four cells a reviewer can read in two seconds. Rows are OR, columns are AND — once that's muscle memory, arbitrarily complex conditions are just a bigger block.

A range on one field needs the header twice

Here's the trap that stops people cold: bounding a single column between two values. The instinct is to write >=100 and <=500 in one cell. That can't work — a cell holds one condition. Two conditions on the same field need the header to appear in two columns:

' Amount between 100 and 500 (inclusive):
'   H1: Amount    I1: Amount     <- the SAME header, twice
'   H2: >=100     I2: <=500
=DSUM(A1:E200, "Amount", H1:I2)

Both are on one row, so they're ANDed — Amount >= 100 AND Amount <= 500. It looks odd to repeat a header, but that's the mechanism: each criteria column carries one comparison, and a between-test is two comparisons on the same field.

Formula criteria: the power feature with two strict rules

Criteria don't have to be constants. A criterion can be a formula that returns TRUE/FALSE per row — "above the overall average," "in the top quartile," "longer than its neighbour." This is the single most powerful thing the D-family does, and it fails instantly if you break either of its two rules.

' Sum Amount for rows ABOVE the average Amount:
'   H1: (blank or a label that is NOT a real header, e.g. "AboveAvg")
'   H2: =E2>AVERAGE($E$2:$E$200)
=DSUM(A1:E200, "Amount", H1:H2)

• Rule 1 — the criteria header must be blank or different from every data header. If you label the formula cell Amount, Excel treats it as a normal criterion on the Amount column and your formula breaks. Formula criteria need a header that is not one of the table's headers (or empty).
• Rule 2 — reference the first data row with a relative reference. Write E2 (the first data row), not E:E or $E2. Excel walks the formula down every row internally; a relative first-row reference is what lets it. Keep the comparison range absolute ($E$2:$E$200) so AVERAGE sees the whole column.

Break rule 1 and you get a wrong answer with no error; break rule 2 and the test evaluates against the wrong row. Get both right and you have live, computed criteria no other conditional-aggregation function offers as cleanly.

The starts-with trap (and wildcards)

Text criteria in D-functions are "begins with," not "equals." A criterion of North matches North, Northwest, and Northern alike — a silent over-match that inflates totals and counts.

'   H2: North      matches North, Northwest, Northern...  (begins-with!)
'   H2: ="=North"  matches ONLY "North" exactly
'   H2: Jo*        wildcard: Jones, Johnson (any chars)
'   H2: ?at        wildcard: cat, bat, hat (one char + "at")

To force an exact match, wrap it as ="=North". * (any number of characters), ? (exactly one), and ~ (escape a literal */?) work as usual. When a DSUM or DCOUNT reads slightly too high, the begins-with default is the prime suspect — right after a header typo reading too low.

The decision tree: D-functions vs SUMIFS vs FILTER

Three tools now answer "aggregate the rows that match." Choosing well:

• SUMIFS / AVERAGEIFS / COUNTIFS — your default. Conditions in the formula, nothing extra on the sheet, easy to copy down a column, fast at scale. Best when the conditions are simple AND logic and won't change often.
• Database functions — when the question should live on the sheet: complex OR branches, formula-based criteria, a criteria block colleagues will edit, or an audit trail where a visible query beats a nested formula. Verbose, but uniquely readable and the only one with DGET's uniqueness guarantee.
• FILTER + aggregation — when you want the matching rows themselves, not just one number, or when the logic is more natural as a boolean array: =SUM(FILTER(Amount, (Region="West")*(Amount>1000))). The modern dynamic-array way, and often the most flexible — but requires Excel 365.

The honest summary: D-functions are legacy but not obsolete. For a repeatable formula dragged down a column, use SUMIFS. For live extracts on 365, use FILTER. But for a one-off, multi-condition, reviewer-readable question on any version of Excel — a question you want to see on the sheet — a criteria block and a D-function are still the clearest tool Excel has.

How ExcelMaster helps

The criteria range is powerful and finicky in equal measure — the header-twice rule for ranges, the blank-header rule for formula criteria, the begins-with default. Describe the question in plain terms — "average order value for West or East orders above the overall average" — and ExcelMaster builds the criteria block correctly: headers matched to your data, AND and OR laid out in the right geometry, formula criteria with the relative first-row reference, and the =DAVERAGE(...) pointed at it. It also reads an existing D-function and tells you why the number looks off — a header typo, a stray blank row, or a begins-with over-match.

Frequently asked questions

What are the database functions in Excel?

They're a family of twelve functions — DSUM, DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP — that aggregate the rows of a table matching a criteria range. All share the signature Dxxx(database, field, criteria); they differ only in the aggregation. The criteria range — a block of headers and conditions on the sheet — is the concept that unifies them.

How do I create a criteria range with AND and OR?

Conditions on the same row are joined with AND; conditions on separate rows are joined with OR. The top row of the block holds headers copied exactly from your data; the rows beneath hold conditions like West, >1000, or >=100. Size the criteria argument to cover every header and condition cell you use.

How do I filter a database function between two numbers?

Put the field's header in two columns and a comparison in each, on the same row: header Amount in H1 and I1, >=100 in H2 and <=500 in I2. Both are on one row, so they're ANDed into "between 100 and 500."

Can I use a formula as a criterion?

Yes. Use a header cell that is blank or different from any real data header, and a formula that references the first data row relatively — e.g. =E2>AVERAGE($E$2:$E$200) to match rows above the average. A blank/different header is what tells Excel it's a formula criterion rather than a label match.

Are database functions obsolete now that we have SUMIFS and FILTER?

Not obsolete, but specialised. Use SUMIFS/AVERAGEIFS for simple, repeatable conditional aggregation, and FILTER (Excel 365) for pulling matching rows or building boolean logic. Database functions still win for complex OR logic, formula-based criteria, DGET's uniqueness check, and questions you want visible and editable on the sheet — and they work in every Excel version.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-20.

Related guides: Excel DSUM & DCOUNT · Excel DGET · Excel SUMIFS · Excel AVERAGEIFS · Excel FILTER