The DMAX function in Google Sheets is a database function, whereas its alternative MAXIFS is statistical. Which one should I choose?

DMAX and MAXIFS are two popular Spreadsheet functions for conditional max.

In addition to these two, we can use Query for the same purpose in Google Sheets. But we usually use it for advanced-level data manipulation.

DMAX or MAXIFS, which function should I use for conditional Max in Google Sheets?

If this question confuses you, then here is the answer.

First of all, make sure that you want to return the max from structured data. Then no doubt, use the function DMAX.

In this sample data, which is structured, you can use the DMAX to find the max value in column D.

Why am I calling this structured data?

• There are no merged cells.
• It doesn’t contain mixed-type data in columns. For example, column A is the date type. So it shouldn’t have text or numeric values in cells.
• The data/table has a top row with field labels.

If your data satisfies this, then you can use database functions.

One more thing, before starting the section on how to use the DMAX function with examples.

Let me explain the purpose of the DMAX and go to its Syntax.

Syntax, Arguments, and Purpose

The purpose of the DMAX function in Google Sheets is to return the maximum value selected from a database table-like array (structured data) using an SQL-like query.

Syntax:

DMAX(database, field, criteria)

Arguments:

database – The array/range containing the data to consider.

field – Indicates which column in the array (database) contains the values to be extracted/operated on.

In other words, it’s the max value column. You can either use the field label or the column number as the field. I prefer the column number.

criteria – An array containing the condition to filter the database values before operating.

Examples of How to Use the DMAX Function in Google Sheets

Below, find a couple of examples of how to use the DMAX database function in Google Sheets.

DMAX without Criteria in Google Sheets

The DMAX formula will return a N/A error when you exclude criteria within it. It is because the function requires all the arguments to work. None of them are optional.

Then how do we exclude criteria in a DMAX formula in Google Sheets?

Formula # 1:

=DMAX(A1:D12,4,{"Material";""})

Just include any one of the field labels and leave the criterion blank. That’s what you can see in the above example formula.

See the same formula, but the criterion is a cell reference

But this can confuse you if the criteria field is a date column. Why?

Blank Date as Criteria in DMAX Formula

I have a two-column structured data, which is the output of the below GOOGLEFINANCE formula.

The formula in A1:

=GOOGLEFINANCE("NSE:HINDPETRO", "price", EDATE(today(),-12), today(), "DAILY")

The above formula returns the stock price of the ticker symbol “HINDPETRO” (NSE India) from the Google Finance database for the past 12 months.

Please concentrate on the formula output, which is what we want right now.

Formula # 2:

=dmax(A1:B,2,E1:E2)

It’s easy to show the blank date criteria in the DMAX function in Google Sheets if the reference is a range (E1:E2) as above.

How to include this within the formula?

It’s a little tricky!

You need to use the comparison operator with a past date.

You can use the date 30/12/1899 as the past date. That means you can replace the criteria with the below formula.

={"Date";">"&text("30/12/1899","dd/mm/yyyy")}

So the formula will be;

=dmax(A1:B,2,{"Date";">"&text("30/12/1899","dd/mm/yyyy")})

What about replacing the ‘database’ in the formula with the GOOGLEFINANCE formula itself?

No doubt, it will work! The reason is the GOOGLEFINANCE output is structured data with field labels.

=dmax(GOOGLEFINANCE("NSE:HINDPETRO", "price", EDATE(today(),-12), today(), "DAILY"),2,{"Date";">"&text("30/12/1899","dd/mm/yyyy")})

Bonus Tip:

To exclude conditions, you can simply specify them as two virtual blank cells.

E.g.:- =dmax(A1:B,2,{if(,,);if(,,)})

You can find more details here – Two Ways to Specify Blank Cells in Google Sheets Formulas.

Single Criterion (Cell Reference and Hardcoding)

I want to find the max value of the material “3/16 SAND”. So I can understand the maximum quantity of this item shipped any day.

=DMAX(A1:D12,4,{"Material";"3/16 SAND"})

If you want to input the criterion in a cell and refer to that in the DMAX formula, then type “3/16 SAND” without double quotes in cell F2.

Multiple Criteria in the Same Field in DMAX Function in Sheets

In the above formula, we have found the max quantity of the item “3/16 SAND”.

Now I want to find the max quantity of the items “3/16 SAND” and “WASHED SAND.”

Just enter this new criterion in cell F3 and replace F1:F2 (criteria range) in the earlier formula with F1:F3.

=dmax(A1:D12,4,F1:F3)

That’s the case when the criteria are within cells.

It’s a little complex to include the criteria from the same field twice when you hardcode it within the DMAX function in Google Sheets.

=dmax(A1:D12,4,{"Material";"3/16 SAND";"WASHED SAND"})

Please try to understand this formula and further reference here – The Ultimate Guide to Using Criteria in Database Functions in Google Sheets.

Multiple Criteria from Two Different Fields in DMAX Function in Sheets

When the conditions are from two different fields, in DMAX, you can use them as below.

The below DMAX formula returns the max value of the material “3/16 SAND” shipped on 01/11/2018.

Just simple right? But wait! You must know how to include multiple criteria within the formula in DMAX in Google Sheets.

Here it is.

=DMAX(A1:D12,4,{"DATE","Material";DATE(2018,11,1),"3/16 SAND"})

The referring or hardcoding criteria in all the Google Sheets DATABASE functions are similar.

So if you have any doubt regarding this, please refer to this tutorial – How to Properly Use Criteria in DSUM in Google Sheets [Chart] or the earlier mentioned one above.

I hope you have enjoyed this DMAX function tips and tricks. Hope to see you again.