How to Use DMAX Function in Excel with Multiple Criteria

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 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.

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

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.

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.

Thus, we have got the maximum Sale value without any 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.

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

• 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.

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.

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

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.

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

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.

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