# Part 1: Introduction to DMAX Function in Microsoft Excel

Definition: DMAX is a database function in Excel that calculates the maximum value in a selected database column based on multiple conditions. This function is an advanced version of the MAX function, which includes criteria.

Purpose: DMAX aims to find the maximum value in a database column where the corresponding values in other rows meet certain conditions.

Syntax & Arguments:

The syntax of the DMAX function is as follows:

syntax
```=DMAX(database, field, criteria) ```

Let’s dive into what these arguments mean:

1. Database: This is the range of cells that make up the database. The database should include column headers.

2. Field: This is the column in the database where the function will be used. The area can be given as text with the column header or as a number that represents the position of the column in the database.

3. Criteria: This is the cell range containing the conditions you specify. It should include at least one column header and one cell below the column header for defining requirements.

Return Value: DMAX returns the most significant number in a set of values based on given criteria.

Remarks: If the database or field argument is not valid or the field does not exist in the database, the DMAX function will return a #VALUE! Error. Also, if no record in the database meets the criteria, DMAX will return a #NUM! Error.

# Part 2: Examples of DMAX Function in Microsoft Excel

## Example 1

Purpose: Find the highest sale in a particular region.

Data Table:

ABCD
1RegionSales
2West500
3East600
4West700
5=DMAX(A1:B4, “Sales”, A1:A2)

Result Table:

D
5700

Explanation: In this example, we want to determine the highest sale in the West region. We have our data in columns A and B, with column A having parts and column B having sales. We have specified the criteria as “West” in cell A2. The DMAX formula is set up in cell D5, referencing the database range A1:B4, the field “Sales”, and the criteria range A1:A2. The result is 700, the highest sale in the ‘West’ region.

## Example 2

Purpose: Find the highest sale in January.

Data Table:

ABCD
1MonthSales
2January1000
3February1200
4January1500
5=DMAX(A1:B4, “Sales”, A1:A2)

Result Table:

D
51500

Explanation: In this example, we want to determine the highest sale in January. We have our data in columns A and B, with column A having months and column B having sales. We have specified the criteria as “January” in cell A2. The DMAX formula is set up in cell D5, referencing the database range A1:B4, the field “Sales”, and the criteria range A1:A2. The result is 1500, which is the highest sale in January.

## Example 3

Purpose: Find the highest sale for product ‘A’.

Data Table:

ABCD
1ProductSales
2A2000
3B2200
4A2500
5=DMAX(A1:B4, “Sales”, A1:A2)

Result Table:

D
52500

Explanation: In this example, we want to find out the highest sale for product ‘A’. We have our data in columns A and B, with column A having products and column B having sales. We have specified the criteria as ‘A’ in cell A2. The DMAX formula is set up in cell D5, referencing the database range A1:B4, the field “Sales”, and the criteria range A1:A2. The result is 2500, the highest sale for product ‘A’.

## Example 4

Purpose: Find the maximum sales value in the East region and display a message based on the result.

Data Table:

ABCD
1RegionSales
2East100
3West200
4East300
5=IF(DMAX(A1:B4, “Sales”, A1:A2) > 200, “High Sales”, “Low Sales”)

Result Table:

D
5High Sales

Explanation: We use the DMAX function nested within an IF function in this example. The DMAX function is finding the maximum sales value in the East region. The IF function then checks if this value is greater than 200. If it is, it returns the text “High Sales”. Otherwise, it replaces “Low Sales”.

## Example 5

Purpose: Sum the maximum sales value from East and West regions.

Data Table:

ABCDE
1RegionSales
2East100West200
3West150East250
4East200West300
5=SUM(DMAX(A1:B4, “Sales”, A1:A2), DMAX(C1:D4, “Sales”, C1:C2))

Result Table:

E
5500

Explanation: In this example, we use the DMAX function within a SUM function. The DMAX functions to find the maximum sales values in the East and West regions. The SUM function then adds these two maximum values together.

## Example 6

Purpose: Retrieve the salesperson’s name with the maximum sales value in the East region.

Data Table:

ABCD
1RegionSalesSalesperson
2East100John
3West200Sarah
4East300Mike
5=VLOOKUP(DMAX(A1:B4, “Sales”, A1:A2), B1:C4, 2, FALSE)

Result Table:

D
5Mike

Explanation: In this example, we use the DMAX function within a VLOOKUP function. The DMAX function is finding the maximum sales value in the East region. The VLOOKUP function then looks for this value in column B and returns the corresponding salesperson name from column C.

## Example 7

Purpose: Check if the maximum sales value in the East region is more significant than a specific target.

Data Table:

ABCD
1RegionSales
2East100
3West200
4East300
5=DMAX(A1:B4, “Sales”, A1:A2) > 250

Result Table:

D
5TRUE

Explanation: In this example, we are using the DMAX function to find the maximum sales value in the East region. The formula then checks if this value is greater than 250. If it is, it returns TRUE. Otherwise, it returns FALSE.

## Example 8

Purpose: Retrieve the product associated with the maximum sales value in the East region.

Data Table:

ABCD
1RegionSalesProduct
2East100A
3West200B
4East300C
5=INDEX(C2:C4, MATCH(DMAX(A1:B4, “Sales”, A1:A2), B2:B4, 0))

Result Table:

D
5C

Explanation: In this example, we use the DMAX function within an INDEX-MATCH function. The DMAX function is finding the maximum sales value in the East region. The INDEX-MATCH function then looks for this value in column B and returns the corresponding product from column C.

## Example 9

Purpose: Calculate the average maximum sales values in the East and West regions.

Data Table:

ABCDE
1RegionSales
2East100West200
3West150East250
4East200West300
5=AVERAGE(DMAX(A1:B4, “Sales”, A1:A2), DMAX(C1:D4, “Sales”, C1:C2))

Result Table:

E
5250

Explanation: We use the DMAX function within an AVERAGE role in this example. The DMAX functions to find the maximum sales values in the East and West regions. The AVERAGE function then calculates the average of these two maximum values.

## Example 10

Purpose: Determine if the maximum sales value in the East region equals the maximum sales value in the West region.

Data Table:

ABCDE
1RegionSales
2East100West200
3West150East250
4East200West300
5=DMAX(A1:B4, “Sales”, A1:A2) = DMAX(C1:D4, “Sales”, C1:C2)

Result Table:

E
5FALSE

Explanation: In this example, we use two DMAX functions to find the maximum sales values in the East and West regions. The formula then checks if these two maximum values are equal. If they are, it returns TRUE. Otherwise, it returns FALSE.

# Part 3: Tips and Tricks

1. Always include column headers in your database and criteria ranges. This helps Excel to understand which column the criteria should apply to.

2. Make sure the criteria range does not include empty cells. If it does, DMAX might return incorrect results.

3. DMAX can handle multiple criteria. Add various measures to the requirements range under the appropriate column header if you want to apply them.

4. DMAX is not case-sensitive. The function does not differentiate between uppercase and lowercase characters when applying criteria.