Can DMAX use multiple criteria at once?

Yes. Add more columns to your criteria range that match database headers. Each column in the criteria row represents an AND condition. For example, to find the max Actual where Category is 'Entertainment' AND Month is 'February', include both headers in the criteria range with those values in the row below.

What happens if no rows match the criteria?

DMAX returns an error or empty result depending on your spreadsheet software. Verify that your criteria values match the data exactly. Check for extra spaces, different capitalization, or typos in category and month names.

Does DMAX work with negative numbers?

Yes. DMAX treats negative numbers normally as numeric values. Among [-20, 10, -5], it returns 10 (the maximum). This is useful for variance or profit columns where some values are negative.

Why use DMAX instead of MAX with a filter or array formula?

DMAX is more readable and doesn't require array syntax. MAX needs nested FILTER or complex IF statements, which are harder to maintain and less portable across Excel, Google Sheets, and other platforms. DMAX explicitly shows you're finding a database maximum with criteria.

DMAX function

Returns the maximum numeric value from a database column where all rows match the specified filter criteria conditions.

=DMAX(database, field, criteria)

Generate a DMAX formula

Describe what you need. The generator will reach for DMAX where DMAX is the right tool, and tell you when it is not.

Arguments

How DMAX reads its arguments

Argument	Required	Description
database	Required	The range containing all data including headers in the first row (e.g., A1:E7 with categories, months, and amounts).
field	Required	The column name as text in quotes (e.g., "Actual") to find the maximum from; must exactly match a header in the database.
criteria	Required	A range with one header row matching database columns and one or more condition rows below; each column in the criteria row filters that database column using AND logic.

Returns

A number representing the largest value in the specified field column where all criteria rows match.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the highest actual spending for Groceries

Category	Month	Budgeted	Actual	Variance
Groceries	January	500	520	-20
Groceries	February	500	480	20
Utilities	January	200	210	-10
Utilities	February	200	205	-5
Entertainment	January	150	100	50
Entertainment	February	150	140	10
Category
Groceries

=DMAX($A$1:$E$7, "Actual", $A$9:$A$10)

Result: 520

DMAX filters to rows where Category is 'Groceries' (January: 520, February: 480) and returns the maximum value from the Actual column. The highest spending is 520 in January.

2. Find the maximum budgeted amount for Entertainment in February

Category	Month	Budgeted	Actual	Variance
Groceries	January	500	520	-20
Groceries	February	500	480	20
Utilities	January	200	210	-10
Utilities	February	200	205	-5
Entertainment	January	150	100	50
Entertainment	February	150	140	10
Category	Month
Entertainment	February

=DMAX($A$1:$E$7, "Budgeted", $A$9:$B$10)

Result: 150

The criteria range includes two headers, so DMAX applies both conditions with AND logic: Category = 'Entertainment' AND Month = 'February'. One row matches, returning its Budgeted value of 150.

3. Find the largest variance (biggest positive difference)

Category	Month	Budgeted	Actual	Variance
Groceries	January	500	520	-20
Groceries	February	500	480	20
Utilities	January	200	210	-10
Utilities	February	200	205	-5
Entertainment	January	150	100	50
Entertainment	February	150	140	10
Category
Entertainment

=DMAX($A$1:$E$7, "Variance", $A$9:$A$10)

Result: 50

DMAX filters to Entertainment rows (variance 50 in January, 10 in February) and finds the maximum in the Variance column. It returns 50, the biggest positive difference (actual spending was furthest below budget).

Common errors

Which DMAX error are you seeing?

Error	Why it happens	How to fix it
#N/A	The field name does not exist in the database headers, such as using "Actual_Cost" when the header is "Actual", or misspelling "Variance" as "Variances".	Check that the field name in quotes exactly matches the header in the first row of your database range, including spacing and capitalization. Copy the header text directly if unsure.
#VALUE!	The database or criteria range is malformed, or the field argument is not a text string. For example, passing a column number instead of "Actual", or omitting quotes around the field name.	Ensure field is supplied as quoted text like "Actual", database is a contiguous rectangle with headers in row 1, and criteria range includes matching header row(s) with data rows below.
#REF!	The database or criteria range references cells that no longer exist, usually because rows or columns were deleted after the formula was written.	Update the range references to point to the current data location. For example, if data moved to A1:E9, change $A$1:$E$7 to $A$1:$E$9.

Tips and when to use something else

Use DMIN instead to find the smallest value matching criteria, or DAVERAGE to find the average of matching rows.
Criteria can include comparison operators: enter ">500" in a criteria cell to match values greater than 500, or "<>Utilities" to exclude a category.
For single-condition filtering, DMAX is clearer than MAX combined with nested IFs. For complex logic across multiple sheets or dynamic criteria, consider XLOOKUP or INDEX/MATCH.
DMAX ignores text values in the field column; ensure you're analyzing a numeric column, or rows with text will be skipped and not affect the result.

Related functions

DMINReturns the minimum value in a database field for rows matching criteria, helping find lowest prices, smallest quantities, or minimum values in filtered records.DSUMDSUM sums values in a database column that meet specified criteria—use it to total filtered column values from a data table.DAVERAGECalculates the average of values in a database field that meet specified criteria, useful for finding filtered averages without creating separate tables.DCOUNTDCOUNT counts cells in a database field that meet specified criteria, returning the total count of matching non-blank cells.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

Can DMAX use multiple criteria at once?: Yes. Add more columns to your criteria range that match database headers. Each column in the criteria row represents an AND condition. For example, to find the max Actual where Category is 'Entertainment' AND Month is 'February', include both headers in the criteria range with those values in the row below.
What happens if no rows match the criteria?: DMAX returns an error or empty result depending on your spreadsheet software. Verify that your criteria values match the data exactly. Check for extra spaces, different capitalization, or typos in category and month names.
Does DMAX work with negative numbers?: Yes. DMAX treats negative numbers normally as numeric values. Among [-20, 10, -5], it returns 10 (the maximum). This is useful for variance or profit columns where some values are negative.
Why use DMAX instead of MAX with a filter or array formula?: DMAX is more readable and doesn't require array syntax. MAX needs nested FILTER or complex IF statements, which are harder to maintain and less portable across Excel, Google Sheets, and other platforms. DMAX explicitly shows you're finding a database maximum with criteria.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17