Buzz
As you may know, the MAX() function helps you retrieve the largest value among input values. However, if you want to get the largest value satisfying a given condition, the MAX() function falls short. To achieve this, you need to utilize the DMAX() function. DMAX() allows you to fetch the largest value that meets specific input conditions.
If you are unfamiliar with the syntax and usage of the DMAX() function, follow along with the discussion in the article below:
Description
The DMAX() function retrieves the largest value in a column within a list or database that satisfies the conditions you specify.
Syntax
=DMAX(database, field, criteria)
Where:
- database: a list or database including column headers.
- field: the column from which to retrieve the largest value. You can directly enter the column header in double quotes or a number indicating the column position in the database: 1 for the first column, 2 for the second column..., you can also enter the cell containing the column header to use, for example, B3, C1...
- criteria: is the range of cells containing the condition. You can choose any range as long as it contains at least one column header and the cell below the column header contains the condition for the column.
Note:
- It is recommended to set the criteria range on the worksheet so that when adding data, the range containing the conditions remains unchanged.
- The criteria range needs to be kept separate and should not be inserted into the list or database that needs processing.
- The criteria must necessarily include at least a Column Header and a cell containing the condition below the column header.
Example
Find the highest salary of female employees in the following data table:
First, create the condition where Gender is Female.
Next, apply the DMAX formula:
=DMAX(B8:F14,F8,D5:D6)
B8:F14 represents the data table to find the highest salary.
F8 corresponds to the Salary column, the column to find the highest salary values.
D5:D6 represents the criteria condition range.
The result will be as follows:
The article has described, explained the syntax, and provided a specific example, hoping that you will understand and know how to use the DMAX() function in Excel. Wish you success!
