Excel is a powerful software as it has predefined functions and numerous features. These functions make the excel experience smoother and more efficient. Just like that, the DMAX function is one of the Excel worksheet functions. After that, in this article, I am going to explain how to use DMAX function in Excel.
The below image shows an overview of the use of DMAX function. Subsequently, I will share examples of using DMAX function in the following article.
Introduction to DMAX Function in Excel
The DMAX function falls under the category of Excel Database functions. This function finds and returns the largest value from the selected entries of a database.
Syntax:
Returns the maximum/ largest value in a specified field from selected database entries that match the provided condition.
Arguments Explanation:
| ARGUMENT | REQUIRED/ OPTIONAL | VALUE |
|---|---|---|
| database | Required | Range of cells where the first row holds the label. |
| field | Required | The database column where the maximum value is searched. |
| criteria | Required | Range of cells where the conditions are specified. Single or multiple criteria can be used as long as the first row contains a label and below that at least one value to satisfy the condition. |
The criteria can be a numerical value, text, or an expression.
2 Easy Examples to Use DMAX Function in Excel
There are some common uses of this function. I am going to provide examples based on single criteria and multiple criteria. Moreover, I have a dataset containing information about the sales of a company, where the attributes are Product Name, Region, Quantity, and Sale Price. For this article, my Excel version is Microsoft Excel 365. You can use any available Excel version.
1. Using Single Criteria
You can use only one criterion when applying this function. For this method, I have a criterion to find out the Maximum Sale of the product Tablet.
Steps:
- First of all, select cell G9.
- After that, type the following formula:
=DMAX(B4:E13, E4, G5:G6)
Formula Breakdown
- DMAX(B4:E13, E4, G5:G6) → The DMAX function will find out the maximum value from the sales of Tablet.
- B4:E13 is the database, where all the necessary information is provided.
- E4 is the field, from which column the value will be found.
- G5:G6 is the specified criteria.
- Output → $9,167.
- Then, press ENTER.
- Therefore, you can see the result.
Read More: How to Use Database Functions in Excel
2. Using Multiple Criteria for DMAX Function
You can also find maximum value using multiple criteria. The criteria range includes Product Name, Region, and Quantity. In addition to using multiple criteria, I am going to use Field by Name, and Field by Index methods. You can also use these methods using single criteria.
i. Field by Name
You can use the label as the field of the function. The DMAX function will match the label with the database itself.
Steps:
- In the beginning, select cell G10.
- After that, type the following formula:
=DMAX(B4:E13, "Sale Price", G5:I7)
Formula Breakdown
- DMAX(B4:E13, “Sale Price”, G5:I7) → The DMAX function will find out the maximum value from the sales of Camera and Monitor.
- B4:E13 is the database, where all the necessary information is provided.
- Sale Price is the label, from which column the value will be found.
- G5:I7 is the specified criteria range.
- Output → $12,762.
- Then, press ENTER.
- As a result, you can see the result.
ii. Field by Index
You can also use the index number to indicate the column in the field of the function.
Steps:
- In the first place, select cell G10.
- After that, type the following formula:
=DMAX(B4:E13, 4, G5:I7)
Formula Breakdown
- DMAX(B4:E13, 4, G5:I7) → The DMAX function will find out the maximum value from the sales of Camera and Monitor.
- B4:E13 is the database, where all the necessary information is provided.
- 4 is the index number, from which column the value will be found.
- G5:I7 is the specified criteria range.
- Output → $12,762.
- Then, press ENTER.
- Hence, you can see the result.
Read More: How to Use DMAX Function in Excel with Multiple Criteria
Things to Remember
- While using the DMAX function, you can not reference a value in a different worksheet or workbook.
- Also, you can not reference a value in a different range.
- Moreover, it is not a case-sensitive function.
Practice Section
On the right side of each worksheet, you will find a practice section to practice on your own.
Download Practice Workbook
You can download the practice workbook here.
Conclusion
In this article, you learned how to use Excel DMAX function with suitable examples. You can suggest your thoughts about this article in the comment section below.
