How To Use The DMAX Function in Google Sheets

Reading Time: 4 minutes

What it does – Returns the maximum value from a range of cells using an SQL-like query

The DMAX function in Google Sheets is the database maximum function that allows users to find the largest value from a well-structured data subject using an SQL-like query. A “well-structured” data is one which satisfies the following requirements:

1. It doesn’t contain any merged cells.
2. There’s uniformity in the data format of each column.
3. The top row of the data represents the column labels.

The article comprises the following sub-sections:

• Steps to use the DMAX function
• Common mistakes
• FAQ

Steps to use the DMAX function in Google Sheets

Let’s take up a task wherein we are required to find the maximum time taken by the Marketing Department to produce a monthly report. Assuming that we have the following dataset, we follow the steps below to use the DMAX function in Google Sheets.

Step 1: Prepare a well-structured dataset with proper column labels.

Step 2: Specify the criteria. In our example, we include the following criteria:

• Department: Marketing
• Frequency: Monthly

Note that the column labels in the criteria must be the same as that in the well-structured dataset.

Step 3: In the cell where you want the result to be placed, input the following formula:

=DMAX(A3:D12,”Time Taken”,G3:H4)

The rows highlighted in the dataset represent those values that satisfy the criteria. The DMAX function in Google Sheets compares the values in the column, “Time taken” and returns the maximum value out of the filtered data.

Alternate Way

The DMAX function would have even worked if, instead of specifying the column, “Time Taken”, as the field of the function, we would have entered the column number (4) as shown below.

=DMAX(A3:D12,4, G3:H4)

Mistakes to avoid

Case #1: The field in the function and the column label must be exactly the same

If the field argument in the function and the column label of the dataset are not the same we encounter the #VALUE! Error in Google Sheets. For instance, let’s consider the following formula and it’s output.

=DMAX(A3:D12, “Time”, G3:H4)

In the formula above, our field argument is “Time” while the concerned column label is “Time Taken”. Due to this discrepancy, when we use the DMAX function in Google Sheets we encounter an error.

The field argument in the DMAX function is not case-sensitive. Hence, if our argument and the concerned column label differ on account of capitalization, it wouldn’t have made any difference.

To know more about the different errors in Google Sheets and how to correct them click here.

Case #2: Using the DMAX function with non-numeric values

If our field argument represented a column that had no numeric values the function would have returned 0 as an output as shown below. For instance, instead of “Time taken” if we use “Department” as the field argument we obtain the following result.

=DMAX(A3:D12, “Departments”, G3:H4)

Since the field, “Department” has only text input, there’s no maximum value. Hence, when we use the DMAX function in Google Sheets, it returns 0 as its output.

Frequently Asked Questions (FAQs)

1. What is the difference between the MAXIF function and the DMAX Function?
   
   The MAXIF function compares two arrays of data and returns the maximum value from the first array that is less than or equal to the maximum value from the second array. On the other hand, the DMAX function returns the maximum value from well-structured data using an SQL-like query.

2. What are some other formulae similar to the DMAX function?
   
   One can use the LARGE function in Google Sheets to find the n^th largest number from a range of cells. The MAX function can also be used to calculate the maximum value from the specified cells. 
   
3. What is the difference between the DMAX function, the MAX function and the LARGE function?
   
   The only difference between the DMAX function and the functions mentioned above is that the DMAX function has the added benefit of filtering out the data which needs to be considered while performing any operations on it.

Conclusion

As mentioned in the beginning the DMAX function is just one of the many database functions that perform a particular operation on a dataset using an SQL-like query. To know more about such functions, check out our blog.

See Also

Here’s a list of some articles that you may find useful according to your interests.

How to use the DSUM function in Google Sheets

How to use the DCOUNT function in Google Sheets

How to use the DGET function in Google Sheets