DMAX in Excel: Find a Selected Maximum and Audit the Winning Group

=DMAX(database, field, criteria)

The database range includes the header row. Field identifies the column to calculate: use a quoted heading such as “Units”, or its one-based position within the database. Here Units is field 3, even if the database is later moved to another part of the sheet. Criteria is a separate range containing matching field headers and at least one conditions row. All three arguments are required.

Enter this numeric database in A1:D7:

Put Team in F1 and Product in G1. In F2 enter ="=North"; in G2 enter ="=A". These formulas produce the exact-match criteria text =North and =A. The range F1:G2 selects the three North/A records, whose Units values are 2, 4 and 6 and Sales values are 100, 200 and 300.

Conditions in the same criteria row act together with AND. Alternative criteria rows act with OR. Keep the criteria and output cells outside the database, and leave room for the data to grow.

The North/A values are 2, 4 and 6, so the selected maximum is 6. The full database contains 10 Units for South/A; that record does not meet the Team criterion.

=DMAX(A1:D7,"Units",F1:G2)
=DMAX(A1:D7,4,F1:G2)

Expected outputs are 6 Units and 300 Sales. Field 4 is Sales. The two maxima happen to be on the same row here, but that is a property of this fixture rather than a guarantee of DMAX.

Put Team in J1 and ="=North" in J2. =DMAX(A1:D7,"Units",J1:J2) gives expected 9, from the North/B record. Removing a condition can increase or leave the maximum unchanged; it does not necessarily preserve the former “winner.”

A useful review table records the criterion alongside each maximum. Without the selected group, an isolated 6 or 9 can be mistaken for a contradictory answer to the same question.

DMAX returns a value, not the complete record that produced it. If a report needs Product or another attribute of a maximum row, construct a separate retrieval step and define how ties should be handled.

In a separate copy, change the first North/A Units value from 2 to 6. The maximum remains 6, but two rows now attain it. A DGET request constrained only by that maximum would be ambiguous. Do not assume that an extreme numeric value is a unique key.

For the original selected values, DMIN gives 2, so =DMAX(A1:D7,"Units",F1:G2)-DMIN(A1:D7,"Units",F1:G2) has expected 4. This range describes the distance between extreme observations, not a standard deviation.

Replacing the middle value 4 with 3 keeps the minimum, maximum and range unchanged while changing the mean and variance. Extremes are useful summaries, but they do not capture the distribution’s center or all of its variability.

To summarize every numeric Units value, create a separate criteria range J4:J5 with Team in J4 and J5 physically blank. Microsoft’s database documentation describes a blank condition row for an unrestricted operation. =DMAX(A1:D7,"Units",J4:J5) then has expected 10.

Use the minimal intended range J4:J5. Do not accidentally append that unrestricted blank row to a more selective criteria range, where it would broaden the result.

The dedicated reference used here does not provide a comprehensive empty-selection or mixed-error table. No exact error or no-match numeric output is claimed. Check matching and numeric counts before interpreting a returned zero as an observed maximum; a missing selection and a real maximum of zero are different situations.

The numeric examples do not certify text coercion, blank targets, hidden input errors or extreme magnitudes. Investigate selection and field identity before adding blanket error suppression.

Use the complete header-and-condition range, not just the condition cells. Check the field heading for spelling and spacing, and verify that a numeric field index still points to the intended column after a layout change. A worksheet’s visible rows are not a substitute for the explicit database range and criteria used in the formula.

Do not include an accidental empty conditions row in the criteria range: an unrestricted alternative can broaden selection. Microsoft’s DAVERAGE reference documents the shared AND/OR, exact-text and case-insensitive matching rules. These examples avoid wildcard, case-sensitive-formula and numeric-text complications.

The database functions do not validate whether the selected records are the right records for the question. Keep a matching-record or numeric-field count beside an important result. Native blank/formula-empty handling, coercion, extreme precision and error propagation not explicitly documented in this guide remain unverified.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft DMAX reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.