The DMax function with example in Excel

The DMax function in Excel is used to find the maximum value in a specified field (column) of a database that matches given criteria.

Syntax

scss
DMax(database, field, criteria)

• database: The range of cells that makes up the database, including the headers.
• field: The column from which to find the maximum value. This can be the column label (enclosed in quotes) or the column number.
• criteria: The range of cells that contains the criteria. The criteria range must include at least one column label and at least one cell below the column label for specifying the condition.

Example

Consider you have the following data in a worksheet:

A	B	C
Product	Sales	Quarter
Apple	500	Q1
Orange	300	Q1
Banana	700	Q2
Apple	800	Q2
Orange	400	Q3
Banana	600	Q3
Apple	900	Q4

You want to find the maximum sales of "Apple".

Step-by-Step:

1. Set Up the Criteria: Let's say you use cells E1 to F2 to define the criteria:

   E	F
   Product	Apple

2. Use the DMax Function: Place the following formula in any empty cell, for example, H1:

   less
   =DMax(A1:C8, "Sales", E1:F2)
   

   • database: A1:C8 (the range of your data including headers)
   • field: "Sales" (the column label for the field you want the max value from)
   • criteria: E1:F2 (the range where the criteria are defined)

Explanation:

• The DMax function looks through the Sales column for rows where the Product column matches "Apple".
• It finds the maximum value of sales for "Apple", which in this case is 900.

By changing the criteria in cells E1 to F2, you can use the same function to find maximum values for different products or conditions.