How to Use Excel DMAX Function (2 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

dmax function in excel


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:

syntax of DMAX function

Returns the maximum/ largest value in a specified field from selected database entries that match the provided condition.

=DMAX(database, field, criteria)

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.

Sample Dataset


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.

outline to use single criteria

Steps:

  • First of all, select cell G9.
  • After that, type the following formula:
=DMAX(B4:E13, E4, G5:G6)

using single criteria

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.

Outline to use multiple criteria in DMAX function


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.

Outline to use Field by Name

Steps:

  • In the beginning, select cell G10.
  • After that, type the following formula:
=DMAX(B4:E13, "Sale Price", G5:I7)

Using Field by Name in DMAX function

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.

Outline to use Field by Index

Steps:

  • In the first place, select cell G10.
  • After that, type the following formula:
=DMAX(B4:E13, 4, G5:I7)

Using Field by Index in DMAX function

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.

Practice section


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rabeya Islam
Rabeya Islam

Hello! I am Rabeya Islam. I have completed my B.Sc. in Computer Science and Engineering from East West University. Currently, I am working as an Excel and VBA Content Developer, and I research MS Excel casually. I have an interest in Research and Development.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo