Quick Navigation

DMAX Function

Excel 2007+

Summary

The DMAX function returns the maximum value from a specified field in a database range that meets your defined criteria. Perfect for extracting peak values from filtered datasets without complex array formulas.

Syntax

DMAX(database, field, criteria)

Parameters

Parameter	Type	Required	Description
database	`Range`	Yes	Complete database range with headers in first row
field	`String/Number`	Yes	Field identifier - column name or position number
criteria	`Range`	Yes	Criteria range with matching headers and conditions below

Using the DMAX Function

DMAX scans your database and returns the highest value from the specified field among records matching your criteria. Use it for sales analysis, inventory tracking, or any scenario where you need maximum values from filtered data.

Common DMAX Examples

Maximum Profit for Apple Trees (Height 10-16ft)

=DMAX(A5:E11, "Profit", A1:B3)

Returns $96 - finds max profit for Apple trees between 10-16 feet tall

Highest Yield for Pear Trees

=DMAX(A5:E11, 4, F1:F2)

Returns 10 - uses column number 4 (Yield) with Pear criteria

Max Age for Trees Over 10ft

=DMAX(A5:E11, "Age", G1:G2)

Returns 20 - oldest tree taller than 10 feet

Frequently Asked Questions

Yes, use formulas like ="Apple" or >100 in criteria cells for text/number conditions.

DMAX returns #NUM! error. Use IFERROR to handle this: =IFERROR(DMAX(...), "No matches")

Yes, criteria headers must exactly match database column labels.

Common Errors and Solutions

#NUM!

Cause: No records match criteria or invalid field

Solution: Check criteria match records and field name/number is correct

#VALUE!

Cause: Database lacks headers or criteria improperly formatted

Solution: Ensure first row has column labels and criteria has matching header

Wrong field returned

Cause: Field specified by number instead of name

Solution: Use column label in quotes: "Profit" instead of 5

Notes

Criteria range cannot overlap database
Don't place criteria below database (blocks new rows)
Blank criteria row processes entire column
Supports <, >, =, >=, <=, <> operators
Case-insensitive text matching

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in:

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+