Excel’s DMAX function returns the largest numeric value in a selected column of a database-like table, but only from rows that meet criteria you specify. It is useful when you need a conditional maximum and want to express the conditions in a separate criteria range.
What DMAX does
DMAX belongs to Excel’s database-function group. It examines a rectangular range with column headings, applies conditions from a criteria range, and returns the greatest qualifying number from the field you choose. For example, it can find the highest sale among orders from a particular region, or the largest quantity for records that meet two conditions.
Unlike MAX, which simply finds the largest number among its arguments or a range, DMAX can filter the records before comparing their values. Unlike a filter applied to a worksheet, the criteria range lets you define conditions without hiding rows or changing the table view. The result is a single value, not a list of the matching records.
DMAX does not require that the source data be formatted as an official Excel Table. However, the data should be organized like a database: one header row, consistent columns, and one record per row. This structure makes both the selected field and the criteria unambiguous.
Syntax and the three arguments
DMAX has three arguments: the database range, the field to evaluate, and the criteria range. The database is the complete table, including its headings. The field identifies the column whose values Excel will compare. The criteria range contains one or more field headings and the conditions underneath them.
- Database: A range that includes headers and all records you want considered. The headers must be in the first row of the range.
- Field: The heading of the column to evaluate, written as text in quotation marks, or its position within the database, starting at 1 for the first column.
- Criteria: A separate range containing at least one matching column heading and at least one condition beneath it.
For clarity, the examples below use a sample table in A1:E9. Its headings are Order, Region, Sales Rep, Units, and Sales. The eight records contain sales of 12,500; 9,400; 17,200; 11,800; 21,000; 15,300; 16,000; and 13,200. The table is illustrative, and the headings and values are assumed to be in those cells exactly as described.
Basic example: largest sale in one region
Suppose G1 contains the heading Region and G2 contains North. That small range is the criteria range: it tells Excel to consider only records whose Region is North. With the sample database in A1:E9 and the field heading Sales in column E, enter this formula:
=DMAX(A1:E9,"Sales",G1:G2)
The result is 17,200. The North records have sales of 12,500, 17,200, and 15,300, so 17,200 is their largest sale. If the field argument were 5 instead of the text heading Sales, the formula would refer to the fifth column of the database range. Using the heading is often easier to read, especially if columns are later rearranged.
Make sure the criteria heading matches a database heading, such as Region. A heading like Area will not refer to the Region field unless that exact heading also appears in the database. A misspelled or mismatched heading can cause the condition not to work as intended.
Using more than one condition
Criteria in different columns on the same row mean AND: a record must satisfy every condition on that criteria row. To find the largest North-region sale greater than 15,000, put Region in F1, Sales in G1, North in F2, and >15000 in G2. Then use this formula with the same A1:E9 database:
=DMAX(A1:E9,"Sales",F1:G2)
The result is 17,200. The formula excludes the North sale of 12,500 because it does not exceed 15,000, and it also excludes the 15,300 sale? No: 15,300 does exceed 15,000, so it remains eligible. The greatest qualifying value among 15,300 and 17,200 is 17,200. This illustrates that a greater-than criterion is strict: a value equal to 15,000 would not qualify.
Criteria on separate rows mean OR. For example, with Region in F1, North in F2, and South in F3, either region can match. Using the same A1:E9 database, the following formula selects the fifth field, Sales:
=DMAX(A1:E9,5,F1:F3)
The result is 21,000, the largest sale among the North or South records. If a criteria range has multiple rows and columns, each row is one possible condition set; conditions within a row are combined, while the rows are alternatives.
Building a criteria range correctly
The criteria range is not just a list of values. Its first row identifies the database fields to test, and the cells below those headings specify the conditions. It can sit anywhere on the worksheet, although placing it near the result formula can make the setup easier to inspect. Keep the criteria range separate from the database so it does not accidentally become part of the records.
- Use the same field names as the database headings, and avoid blank headings or duplicate names where possible.
- Leave at least one criteria row below the headings. A heading by itself does not state a condition.
- Put conditions that must all be true on the same row; put alternative condition sets on different rows.
- For comparisons, use operators such as >, >=, <, <=, =, or <> before the value in the criteria cell.
- For text criteria, enter the text to match. Database criteria can also use wildcard characters such as * for a sequence of characters and ? for one character.
For instance, a criterion of >15000 in a cell under the Sales heading means sales greater than 15,000. If you want to include 15,000 itself, use >=15000 instead. When the condition involves a date, use a valid Excel date value or a comparison criterion that Excel recognizes as a date; date display formats can vary by regional settings.
What values DMAX considers
DMAX returns the largest numeric value in the selected field among rows that meet the criteria. It ignores text and blank cells in that field when looking for a numeric maximum. Dates in Excel are stored as serial numbers, so a date field can also be evaluated numerically; the returned value may need date formatting to display as a date rather than as a serial number.
Text in the field is not converted into a number merely because it looks numeric. If a supposed amount is stored as text, it may be ignored, which can make the result appear unexpectedly low or produce zero when there are no qualifying numeric values. Check the underlying data type if the output does not match the visible records. Also check for errors in source data and for criteria that leave no numeric records eligible; a DMAX result of 0 can indicate that no qualifying numeric value was found, not necessarily that a matching record has a genuine zero.
DMAX compared with MAX and MAXIFS
MAX is the simpler choice when you need the highest value in a range and do not need conditional filtering. It does not use a criteria table. DMAX is useful when the conditions are naturally represented in a criteria range, particularly when you want multiple conditions or OR alternatives laid out visibly on the worksheet.
MAXIFS is another conditional-maximum function. It places the maximum range and condition ranges directly in the formula rather than using a database range plus a separate criteria block. For a straightforward condition, MAXIFS can be more compact and easier to maintain. DMAX can be preferable when the criteria area itself is an important part of the worksheet, or when you are already using related database functions such as DSUM or DAVERAGE. Choose based on how clearly the workbook communicates its conditions, not on the assumption that one function is always faster or better.
Common problems and ways to troubleshoot
If DMAX returns an unexpected value, check the setup in a consistent order. First confirm that the database range includes the header row and all intended records. Next verify that the field argument points to the intended column, and that every criteria heading matches a source heading exactly. Finally, inspect the conditions and the data types in the selected field.
- Wrong or zero result: The criteria may not match any records, or the qualifying field values may be text rather than numbers.
- Too many records qualify: Check whether criteria intended as AND conditions were accidentally placed on separate rows, which makes them alternatives.
- Too few records qualify: Check for extra spaces in text, a strict > condition where >= was needed, or dates entered in a format Excel does not recognize.
- Wrong field is evaluated: Confirm the field heading spelling or count the field position from the first column of the database range, not from column A of the worksheet.
- Results change after edits: Ensure the database and criteria references still cover the correct cells after inserting or moving rows and columns.
For a durable workbook, keep the data headers unique and avoid inserting totals or notes among the records. A clean rectangular source range reduces ambiguity and makes it easier to verify why a particular row did or did not qualify.
Frequently Asked Questions
What does DMAX stand for in Excel?
DMAX is the name of an Excel database function that finds the maximum value in a selected field for records meeting criteria. The “D” distinguishes it from ordinary functions such as MAX that do not take a database-style criteria range.
Can DMAX use multiple criteria?
Yes. Add a heading for each field you want to test. Put conditions that must all be met in the same criteria row; use separate rows for alternative condition sets.
What happens if no records match the criteria?
There is no qualifying numeric maximum to return, so DMAX returns 0. Check the criteria range and source data before interpreting zero as an actual measurement or sale.
Can the field argument be a number?
Yes. It can be the field’s position within the database range, counted from left to right beginning with 1. A heading such as Sales in quotation marks is often clearer when the formula is reviewed later.
Does DMAX work with dates?
It can evaluate dates because Excel stores them as numbers. If the result is a date serial rather than a readable date, apply an appropriate date number format to the result cell.
Should I use DMAX or MAXIFS?
Use DMAX when a separate criteria range suits your analysis or you need its row-based AND/OR structure. Use MAXIFS when direct conditions in a formula make a simple conditional maximum clearer.
Related
- Construction Profit and Loss Template Excel: How to Use
- Conditional Formatting in Excel for a Specific Date
- Excel Conditional Formatting Icons: How to Use Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Table Filter Button Greyed Out? Fixes That WorkIf Excel’s filter button is greyed out, first press Esc to leave cell-edit mode, ungroup any selected worksheets,…
- Number Sequence in Excel: Fill Series or Use a FormulaTo create a number sequence in Excel, enter the first two numbers, select both cells, and drag the…
- Excel Align Left Shortcut: Windows and Mac GuideIn Excel for Microsoft 365 on Windows, select the cell or range and press Alt, then H, A,…
- WORKDAY Function in Excel: Dates, Holidays and ExamplesUse Excel’s WORKDAY function to return a date a specified number of working days before or after a…
- Filter Only Bold Text in Excel with a Helper ColumnTo filter for bold cells in Excel, add a helper column that labels each cell as Bold or…
- How to Filter in Excel to Find the Exact Data You NeedTo filter in Excel, click a cell in your data, then choose Data > Filter; use the arrow…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.