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

Syntax
=DMAX(database, field, criteria)

database: It represents the range of data that is to be considered. The database should be properly structured, i.e., the first row of the database should contain column labels.

field: It represents the column which contains the values that are to be extracted or operated on.

criteria: The range of cells that contain the user-specified conditions

Sample Usage
=DMAX(A2:D10, 2, F2:G4)
//This will return the maximum value from the 2nd column of the data in the cells A2:D10 subject to the fulfilment of conditions specified in the cells F2:G4

Sample Google Sheets template with formula is here.

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

Dataset which will be considered to use the DMAX function in Google Sheets
Sample dataset

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

Preparing a well-structured dataset with proper column labels
Preparing a well-structured dataset

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.

Specifying the criteria
Specifying the criteria

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

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

Using the DMAX function to obtain results
Use the DMAX Function in Google Sheets

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)

When a numeric value is inputted as the Field argument
When the column number is used as the field

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.

When the field argument and column label are different
Using DMAX with different Field and column labels

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)

Using the DMAX function to compare text inputs
When the column label has only text input

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.
  1. What are some other formulae similar to the DMAX function?

    One can use the LARGE function in Google Sheets to find the nth largest number from a range of cells. The MAX function can also be used to calculate the maximum value from the specified cells. 
  2. 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