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

Download Practice Workbook

You can download and practise this workbook.

Step by Step Procedures to Use DMAX in Excel with Multiple Criteria

For this DMAX function, we will follow step by step procedure. We will show how to use the function under different criteria.

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.

• And the last two columns represent Unit Sold and Sale information.

Read More: How to Use Database Functions in Excel (With Examples)

Step 2 : Setting Criteria Table

The criteria table will define 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.

Similar Readings

• How to Use DSUM Function with Multiple Criteria in Excel
• Can Excel DGET Return Multiple Records [See 4 Solutions]
• How to Use DOLLAR Function in Excel (5 Suitable Examples)
• Use EDATE Formula for Days (3 Ideal Examples)
• How to Use DCOUNT Function in Excel (5 Suitable Examples)

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

Read More: How to Use DMIN Function in Excel (4 Suitable Examples)

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

Conclusion

So, we have shown you how to use the DMAX function in 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.

Related Articles

• How to Calculate Standard Deviation with IF Conditions in Excel
• Use DSUM Function with Dynamic Criteria in Excel
• How to Use DPRODUCT Function in Excel (6 Practical Examples)
• Use DVAR Function in Excel (2 Suitable Examples)
• How to Use Macaulay Duration Formula in Excel (2 Easy Methods)
• Use DSTDEV Function in Excel (3 Practical Examples)
• How to Determine If a Number Is Even in Excel (4 Suitable Ways)