How to Use DMAX Function in Excel with Multiple Criteria

Get FREE Advanced Excel Exercises with Solutions!

The DMAX function is used for finding the maximum value of a column or field under multiple criteria. In this article we will show you how you can use the DMAX function in Excel worksheet under multiple criteria. We can use this function to find the maximum sales of a company under multiple criteria.

for this below dataset, we can find the maximum value under multiple criteria as shown in the below image.

DMAX function overview


DMAX Function in Excel: Step by Step procedure

The DMAX function is one of the Database Functions. We will show how to use the function under different criteria with step-by-step procedure.


Step 1: Creating Base Dataset for Performing DMAX in Excel with Multiple Criteria

For this procedure, we have to take a dataset of many variables. So, we have taken this dataset of sales information of a company which will include 4 columns.

The first two columns consist of Date and Seller information.

Creating Dataset for DMAX Function

  • The last two columns represent Unit Sold and Sale information.

Inserting Data for DMAX Function


Step 2: Setting Criteria Table

The criteria table will define the criteria for the DMAX function. This table is an integral part of the DMAX formula.

  • So, Add a criteria table like the below image where the table header should be exactly the same as the data table.

Adding Criteria Table for DMAX Operation


Step 3: Finding Maximum Value with DMAX in Excel with Multiple Criteria

The DMAX function can be used under various criteria. Let’s use different criteria within our dataset to find the maximum value.


DMAX with No Criteria

Now, we will not use any criteria. We are going to find out the maximum Sale with No Criteria.

  • So, write the following formula in G5 and Press ENTER.

=DMAX(B4:E13,"Sale",B15:E16)

  • This formula finds the maximum value of a field from a dataset within a given criteria.

Inserting Formula with DMAX Function

Thus, we have got the maximum Sale value without any criteria.

Finding Maximum Value with No Criteria


DMAX with Multiple Criteria

First, let’s set Multiple Criteria in the criteria table.

  • We want to find out the maximum Sale of Tom when his Unit Sold was less than 13.
  • Write Tom in the Seller field C16
  • And, write < 13 in D16 and Press ENTER.

Setting Multiple Criteria for DMAX Function

Therefore, we have got the maximum value under this Multiple Criteria which is $1600.

Result Output for DMAX Function with Multiple Criteria

  • Let’s use another Multiple Criteria.
  • We want to find out the maximum sale on 11-Jan-23 when the Unit Sold is less than 20.
  • So, write the date 11-Jan-23 in B16 and < 20 in D16 and Press ENTER.

Doing so, we have got the maximum sale on 11-Jan-23 when the Unit Sold is less than 20 and which is $2100.

Result Output for DMAX Function with Multiple Criteria


DMAX with Single Criteria

  • Now, we will use single criteria.
  • First, let’s find out the maximum Sale for a given date.
  • So, Write a date (12-Jan-23) in the Date field of the criteria column and Press ENTER.

Setting Single Criteria for DMAX Operation

So, we get the maximum Sale for (12-Jan-23) and which is $1800.

Showing Output of DMAX Function with Single Criteria

Now, try for other single criteria like the Seller criteria.

  • Fill the Seller criteria with the seller’s name.
  • We want to find the maximum sale by Tom.
  • So write Tom in C16 and Press ENTER.

Setting Single Criteria for DMAX Function

  • Therefore, we get the maximum Sale of Tom which is $1700.

Result Output Using DMAX Function

You can also set other single criteria Unit Sold and Sale.


Practice Section

You can download the practice workbook from the download section and practice the procedure yourself.

Showing Practice Section


Download Practice Workbook

You can download and practice this workbook.


Conclusion

So, we have shown you how to use the DMAX function in an Excel worksheet with multiple criteria. Thank you for reaching this far. We hope you find the content of this article useful. If you have further queries or suggestions, please do mention them in the comment Section. Have a good day.


<< Go Back to Excel DMAX Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo