What it does – Returns the maximum value from a range of cells using an SQL-like query
=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
=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:
- It doesn’t contain any merged cells.
- There’s uniformity in the data format of each column.
- 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.
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:
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.
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.
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)
- 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.
- 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.
- 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.
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.
Here’s a list of some articles that you may find useful according to your interests.