How to use the DMAX function to find the maximum value that satisfies a given condition in Excel

How to use the DMAX function to find the maximum value that satisfies a given condition in Excel: The DMAX function in Excel is used to search for the maximum value with accompanying conditions in a column or row of a list, dataset, or table. Users can find the maximum value in a data field from a Database worksheet to satisfy a pre-defined Criteria chosen by the user. The following article will guide you on how to use the DMAX function in Excel.

1. DMAX function structure

Function syntax: =DMAX(database; field; criteria)
Where:

• database: Required argument, the related list or database that includes column headers.
• field: Required argument, the field (column) from which to retrieve the maximum value. You can directly enter the column header in double quotation marks, or a number representing the column position in the database, or you can enter the cell
• containing the column header to be used.
• criteria: Required argument, the range of cells that contain the criteria. You can select any range as long as it contains at least one column header and a cell below the column header that contains the condition for the column.

Note:

• It is recommended to place the criteria range on a worksheet so that it does not change when adding data.
• Although the criteria range can be positioned anywhere on the worksheet, the criteria range needs to be separate and not inserted into the list or database being processed.
• The criteria range must contain at least one column header and one cell containing the condition below the column header.

2. How to use the DMAX function

Let’s consider an example with a data table as shown below, and the requirement is to find the highest amount for the “Stone” product.

First, we need to create the condition for the “Stone” product.

Next, we apply the DMAX formula:

=DMAX(A1:F11; F1; D14:D15)

Where:

• A1:F11 is the data table.
• F1 is the column “Amount”, the column from which we want to find the highest value.
• D14:D15 is the criteria range.

The result will be the highest amount for the “Stone” product, as shown below:

Alternatively, using the same formula, we can replace “field” with the number 6, which represents the position of the “Amount” column in the table.

=DMAX(A1:F11; 6; D14:D15)

We will obtain the same result as before.

How to use the DMAX function to find the maximum value that satisfies a given condition in Excel

That’s it! The above article has guided you on how to use the DMAX function to find the maximum value that satisfies a given condition in Excel. Good luck!