DMAX Function

All versions Database

The Excel DMAX function returns the largest value in a field for matching rows — using a database table and a separate criteria range. It is the database-style cousin of SUMIFS/AVERAGEIFS, ideal for complex multi-column conditions driven by a criteria block.

Quick answer: finds the maximum of the Amount field for all West-region rows:

=DMAX(A1:D9, "Amount", F1:F2) // criteria F1:F2 holds "Region" then "West"

Syntax

=DMAX(database, field, criteria)

Argument		Description
`database`	Required	The table including its header row (e.g. A1:D9).
`field`	Required	The column to operate on — a header name in quotes ("Amount"), a column number (4), or a cell reference.
`criteria`	Required	A range with a header row plus condition rows (e.g. F1:F2).

How to use it

Using the sales database below (A1:D9) and a criteria range in F1:F2 (header “Region” with “West” beneath it):

	A	B	C	D
1	Region	Rep	Product	Amount
2	West	Maria	Displays	$1,850
3	East	James	Video	$920
4	West	Maria	Accessories	$2,400
5	North	Devon	Displays	$1,310
6	West	Priya	Video	$480
7	East	James	Displays	$3,175
8	North	Devon	Accessories	$760
9	West	Maria	Video	$1,490

=DMAX(A1:D9, "Amount", F1:F2) // finds the maximum of Amount for "West" under Region

How criteria ranges work: the third argument is a small range whose top row repeats your column headers and the rows below hold conditions. >100 under “Amount” means amount over 100; conditions on the same row are AND, conditions on separate rows are OR. This is the same criteria model as Advanced Filter.

Add a second condition for AND logic — put “Product” in G1 and “Video” in G2, then use F1:G2 as the criteria range. Compare with SUMIFS-style functions when your conditions are simple.

Try it: interactive demo

Live demo

Pick a region criterion and see the Amount-field result over the sample database.

Region criterion

Result:

Practice workbook

📊

Download the free DMAX practice workbook

Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

What is the criteria range in DMAX?

A small range whose first row repeats column headers and whose rows below hold conditions. Same row = AND, separate rows = OR.

DMAX vs SUMIFS/COUNTIFS-style functions?

The D-functions use a visible criteria block (great for many or changing conditions and for non-technical users); the IFS functions put conditions inline. Both are valid; choose by readability.

How do I specify the field?

Three ways: the header text in quotes ("Amount"), the column number counting from the left of the database (4), or a cell holding the header.

Can I use wildcards or comparisons in criteria?

Yes — >100, <=50, <>0, and text wildcards like Ja* all work in the condition cells.

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: DSUM · DAVERAGE · DGET · SUMIFS · FILTER