Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

DMAX function overview


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.

Creating Dataset for DMAX Function

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

Inserting Data for DMAX Function

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.

Adding Criteria Table for DMAX Operation


Similar Readings


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.

=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

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.

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


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

Sourav Kundu

Sourav Kundu

Bio: Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo