DMIN Function in Google Sheets: Usage and Criteria Tips

Like SQL, you can interact with a database-style array using the DMIN function in Google Sheets. All database functions in Google Sheets are designed for this purpose.

The DMIN function in Sheets returns the minimum value from a database-style array, similar to how a SQL query operates.

You may be unfamiliar with the term “database-style array” mentioned above. It refers to source data, or records, arranged in rows. However, like a database table, the first row of the range must contain labels representing the values in each column. In short, it’s a range with no merged cells and a header row, or it could be the new Google Sheets Table (Insert > Table).

DMIN Sample Dataset 1 in Google Sheets

The above sample data shows the date-wise diesel consumption or refilling for a few vehicles. Notice that the first row contains column labels.

In data like this, you can use database functions, including DMIN, effortlessly.

If labels aren’t present, you may need to use MINIFS instead.

Syntax of the DMIN Database Function in Google Sheets:

DMIN(database, field, criteria)

Arguments:

  • database – The range containing the data, which adheres to the database table-like array structure mentioned above.
  • field – The column in the database to find the minimum value. You can use either the column label or the column number. The first column in the database is numbered 1.
  • criteria – The condition used to filter the database.

Let me explain how to use the DMIN function in Google Sheets along with some tips for working with criteria.

Examples

Let’s consider the same sample data. The first column contains the fuel filling dates labeled ‘date’, the second column contains the filled quantities in gallons (qty_gallon), and the third column contains the vehicle numbers (vehicle_no). Below are tips on how to use criteria with this function.

DMIN Function and String Criteria in Sheets

The formula below returns the minimum fuel quantity filled by the vehicle “TMP 597”:

=DMIN(A1:C15, 2, {"vehicle_no"; "TMP 597"})

Alternatively, if you want to use a cell reference for the criterion, where E1 contains “vehicle_no” and E2 contains “TMP 597”, the formula will be:

=DMIN(A1:C15, 2, E1:E2)
Text Criteria in DMIN Function in Google Sheets

There are three common types of criteria: text, number, and date. Sometimes, you may also need to use comparison operators.

Above, I’ve demonstrated how to use text criteria in DMIN. Now, let’s explore a DMIN formula using a date as the criterion.

DMIN Function and Date Criteria in Sheets

This formula filters the rows that contain the date 17/08/2018 in the first column, and then finds the minimum value in the second field:

=DMIN(A1:C15, 2, {"date"; DATE(2018, 8, 17)})

The date is specified using the DATE function, with the syntax DATE(year, month, day).

Here’s the equivalent formula when E1 contains ‘date’ and E2 contains 17/08/2018:

=DMIN(A1:C15, 2, E1:E2)
Date Criteria in DMIN Function in Google Sheets

Similarly, you can use a TIME criterion. When hardcoding, use the time in the format TIME(hour, minute, second).

DMIN Function and Number Criteria in Sheets

For this example, we’ll use a new sample dataset with ‘Player Name’, ‘Matches’, and ‘Goals’ as field labels. The data range is A1:C6.

In the following DMIN formula, E1 and E2 contain “Matches” and 5, respectively:

=DMIN(A1:C6, 3, E1:E2)
DMIN Function with Number Criteria in Google Sheets

The formula returns the least number of goals scored by any player in 5 matches. Alternatively, the formula can be hardcoded as:

=DMIN(A1:C6, 3, {"Matches"; 5})

DMIN Formula with Comparison Operators

Here’s an example of using a comparison operator with the DMIN function for a numeric criterion in Google Sheets:

=DMIN(A1:C6, 3, {"matches"; ">4"})

When referring to a cell, you can directly type the criteria with the comparison operator:

For instance, if E1 contains “matches” and E2 contains “>4”, use:

=DMIN(A1:C6, 3, E1:E2)
DMIN Function with Comparison Operators in Google Sheets

This approach also applies to date fields.

Additional Tips: No Criteria and Multiple Criteria Usage

In the examples above, we used a single criterion. But what if you don’t want to specify any criterion?

To exclude criteria, you can simply specify the field label and leave an empty cell below it. Refer to this range in the formula.

When hardcoding, you can specify criteria using two empty cells, such as {IF(,,); IF(,,)}.

For multiple criteria from two different fields, enter the field labels in E1:F1 and the relevant criteria below them.

If you have multiple criteria for the same field, enter the field label in E1 and list the criteria below it, one after another.

For hardcoding multiple criteria, please refer to: The Ultimate Guide to Using Criteria in Database Functions in Google Sheets.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.