DMAX Formula in Excel: Syntax, Examples, and Criteria

Formulas & functions · Updated November 22, 2026

Excel’s DMAX function returns the largest numeric value in a database field among records that meet specified criteria. For example, with a headered data range in A1:C7 and criteria in E1:E2, use =DMAX(A1:C7,3,E1:E2) to return the largest matching value from the third column.

What DMAX Does

DMAX is one of Excel’s database functions. It examines a list of records, checks which rows satisfy conditions you specify, and returns the greatest numeric value from a chosen field in those rows. It is useful when a regular maximum calculation needs to consider several filters, especially when criteria are arranged in a small worksheet range.

For example, imagine a sales list with columns for Region, Rep, and Sales. You might ask for the largest sale made in the East region, or the largest sale in the East region by a particular representative. DMAX can answer either question without manually filtering the source data first.

DMAX is not the same as MAX. MAX returns the greatest number in the cells you give it, whether or not their rows match a condition. DMAX evaluates records against a criteria range before taking the maximum. It also expects a database-style layout: one header row followed by records, with each record occupying a row.

DMAX Syntax and Arguments

For the general function syntax, the database is a range with field headers in its first row, field identifies the column to evaluate, and criteria is a separate range containing headers and conditions: =DMAX(database,field,criteria). The words in this syntax describe arguments; they are not cell references unless you have named ranges with those exact names.

  • database: The complete list range, including its header row. Every row below the headers is treated as a record.
  • field: The column whose numeric values DMAX will compare. You can enter the header name in quotation marks, such as "Sales", or its position in the database, such as 3 for the third column.
  • criteria: A worksheet range containing at least one header that matches a database header and one or more criteria rows beneath it.

Using the header name is often clearer when a formula is read or maintained later. A column number can be convenient in a compact formula, but it is relative to the database range: 1 means its first column, not worksheet column A. If columns are rearranged, check that the field argument still points to the intended field.

A Basic DMAX Example

Suppose A1:C7 contains these headers and records: Region, Rep, and Sales. The sales values are North/Alice/420, South/Ben/510, East/Cara/680, West/Dan/390, East/Eva/740, and East/Finn/625. In E1:E2, enter the matching header Region and the condition East. The database range includes the header row and all six records.

With that layout, the formula below selects the Sales field by its position in A1:C7. It returns 740, because 740 is the largest Sales value among the East records (680, 740, and 625).

=DMAX(A1:C7,3,E1:E2)

To make the formula easier to understand at a glance, you can refer to the field by its header instead. With the same data and criteria ranges, this version also returns 740:

=DMAX(A1:C7,"Sales",E1:E2)

How to Set Up Criteria

The criteria range is central to DMAX. Its header must correspond to a field in the database, and the cells below the header describe which records qualify. The spelling of the header should match the source header; using the same text avoids ambiguity. The criteria range can sit anywhere on the worksheet, but it must remain separate from the data list.

Conditions on the same criteria row are combined as AND: a record must satisfy every condition in that row. Separate criteria rows are combined as OR: a record can satisfy one row or another. This is a powerful way to express multiple conditions without building a long formula.

For a two-condition example, keep the database in A1:C7 with Region, Rep, and Sales headers. Put Region and Sales in E1:F1, then put East and >500 in E2:F2. With that layout, this formula returns 740: only East records with Sales greater than 500 qualify, and their values are 680, 740, and 625.

=DMAX(A1:C7,"Sales",E1:F2)

For OR logic, enter East on one criteria row and West on another under a Region header. DMAX then includes records from either region. In the example data, the largest qualifying sale is still 740. A blank criteria cell does not impose a condition on that field, so it can be useful to combine a condition on one row with a different condition on another.

Criteria Operators, Text, and Dates

Criteria cells can contain exact text or comparison conditions. For numeric comparisons, enter an operator followed by a value, such as >500, >=500, <500, <>500, or =500. The comparison is applied to the corresponding field in each record. For an exact text criterion, enter the text itself, such as East.

For text matching, Excel criteria can use wildcards: an asterisk matches any number of characters, a question mark matches one character, and a tilde can be used to treat a wildcard character literally. For instance, a criterion such as East* matches text beginning with East. Be cautious with broad wildcard conditions: they may include more records than intended.

Dates are stored as serial numbers in Excel, so date comparisons can be used as criteria for a date field. A criteria header must match the date column’s header, and the condition must be a date or a comparison that Excel can interpret as a date. If a date condition is not behaving as expected, check that the source entries are actual Excel dates rather than text that only looks like dates, and confirm the workbook’s regional date interpretation.

Choosing the Field and Preparing Data

The field argument determines which column supplies the numbers being compared; the criteria determine which rows are considered. These are separate jobs. If you want the highest sale among East records, use Sales as the field and Region = East as the criterion. Putting Region as the field would ask for a maximum from the Region column instead, which is not the intended numeric calculation.

Keep the database rectangular and consistent. Use a single header row, avoid merged cells in the list, and place each record on its own row. Blank rows inside a list, duplicate or unclear headers, and totals mixed into the records can make results harder to interpret. If a total row is included in the database, DMAX may treat it as another record when it meets the criteria; usually it is better to exclude totals from the database range.

DMAX is designed to return the greatest numeric value in the selected field. Text and blank cells in that field do not provide numeric candidates. Dates can be compared numerically because Excel stores them as numbers, but make sure the selected field contains consistent values and that its formatting makes the result understandable.

Common Problems and Checks

  • The result is zero: Check that at least one record meets the criteria and that the chosen field contains numeric values for those records. DMAX returns 0 when there are no matching numeric values to return.
  • The result is unexpectedly high or low: Verify the database boundaries, field argument, and criteria rows. A field number counts from the left edge of the database range. Also check whether a totals row or an unintended record was included.
  • A condition seems ignored: Confirm that the criteria header matches a database header and that the condition is beneath the correct header. Conditions meant to be AND must be on the same criteria row; putting them on separate rows creates OR logic.
  • Text or numbers fail to match: Check for extra spaces, inconsistent spelling, numbers stored as text, and date values stored as text. Criteria may look right on screen while the underlying cell values differ.
  • The formula shows an error: Check that all three arguments are present, that the field name exists in the database header row, and that the criteria reference includes its header. If the formula uses commas but Excel expects semicolons because of regional settings, use the separator Excel accepts.

When debugging, first test a simple criterion on one field, then add further conditions. This helps distinguish a problem with the source range from a problem with AND/OR arrangement. If you copy the formula elsewhere, consider using absolute references for ranges—for example, by selecting a range in the formula and pressing F4 on Windows. On Mac, the equivalent shortcut is typically Command+T in formula editing; keyboard behavior can depend on the keyboard and Excel settings.

DMAX Compared with MAX and MAXIFS

Use MAX when you need the largest value in a set of cells and no row-level conditions are involved. Use DMAX when the data is arranged as a database and you want criteria stored in worksheet cells, especially when criteria combinations or wildcard matching matter.

MAXIFS is often a more direct alternative for ordinary conditional maximums. It puts the maximum range and condition ranges into the formula itself rather than using a separate criteria block. For example, if Sales is in C2:C7 and Region is in A2:A7, this formula returns 740, the highest sale for East:

With sales in C2:C7 and region names in A2:A7, enter =MAXIFS(C2:C7,A2:A7,"East"). MAXIFS is available in Microsoft 365 and Excel 2019 or later. DMAX can be preferable when criteria should be visible and editable in cells, or when you want the database-function criteria layout. MAXIFS can be simpler for a fixed, straightforward condition. Neither function replaces careful range design: both need the right field and conditions.

Frequently Asked Questions

Does DMAX include the header row in its calculation?
Use the header row as part of the database range because DMAX needs it to identify fields. The header is not treated as a record value; the data records begin in the rows below it.

Can DMAX use more than one condition?
Yes. Put each condition under its matching header in the criteria range. Conditions on the same row mean all must be met, while separate rows represent alternatives.

What does DMAX return if no records match?
DMAX returns 0 when no qualifying numeric value is available. That can be indistinguishable from a genuine maximum of zero, so check the criteria and matching records if zero is surprising.

Can the field argument be a column number?
Yes. The number is counted from the first column of the database range: 1 means the first field, 2 the second, and so on. You can also use a field header in quotation marks, which can make the formula more readable.

Is DMAX available in Excel for the web and on Mac?
DMAX is an established Excel database function and is available in current Excel for Microsoft 365, including Excel for the web and Excel for Mac. The function syntax is the same, although menus and keyboard shortcuts can differ by platform.

When DMAX Is a Good Fit

DMAX is a useful choice when your records already have clear headers and you want the criteria to remain visible in cells rather than embedded in a formula. It can handle one condition, several AND conditions, or alternative OR conditions through its criteria range. For a simple maximum with fixed conditions, MAXIFS may be easier to read; for a criteria-driven database calculation, DMAX offers a compact and flexible approach. In either case, a well-structured list and carefully checked criteria are what make the result reliable.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.