The DMAX function is used to find the maximum value in a database or list that meets specified criteria. It is handy for performing data analysis and filtering within Excel databases.


=DMAX(database, field, criteria)

database Range of cells that make up the database including headers.
field The column or field from which to find the maximum value.
criteria Range of cells that defines the conditions to be met for extracting the maximum value.

About DMAX

When you're tasked with sifting through extensive data sets in Excel, DMAX swoops in as your ally in identifying the highest value that fits your defined criteria. This function proves especially beneficial when parsing through databases or lists to pinpoint the topmost value based on specified conditions, aiding in efficient data analysis and decision-making processes within Excel workbooks. To effectively utilize DMAX, you provide it with the database range, specify the field containing the values you wish to evaluate, and define the criteria that the data must meet to be considered for the maximum value extraction. In essence, DMAX empowers you to extract valuable insights by isolating the maximum value within the designated data range while adhering to specific filter conditions you set. It's a versatile tool for extracting key information in a dynamic and controlled manner within Excel.


Suppose you have a database of sales records with columns for 'Product', 'Sales Rep', and 'Net Sales'. You want to find the highest net sales figure for products sold by a specific sales representative. The DMAX formula would be: =DMAX(A1:C100, "Net Sales", A1:A100, "=John") This will return the maximum net sales value for products sold by the sales representative named John.

Imagine you have a database of employee information with columns for 'Name', 'Department', and 'Salary'. You aim to determine the highest salary within the Marketing department. The DMAX formula would be: =DMAX(A1:C50, "Salary", A1:A50, "=Marketing") This will provide you with the maximum salary within the Marketing department based on the specified criteria.


How does the DMAX function identify the maximum value within the specified criteria?

The DMAX function scans the database for values that meet the defined criteria and then selects the highest value from the chosen field or column, providing it as the output result.

Can the DMAX function be used for multiple criteria filtering?

Yes, the DMAX function can handle multiple criteria for filtering by including additional conditions within the criteria range. It allows for the precise extraction of the maximum value based on combined criteria.

What happens if no values meet the specified criteria in the DMAX function?

If the DMAX function does not find any values in the database that meet the specified criteria, it returns a #VALUE! error to indicate that no maximum value could be identified under the given conditions.

Related functions