In many situations, we need to use the DMIN function to find the minimum value in a range of cells based on criteria. In the following article, we will describe 4 easy uses of the DMIN function in Excel.
Overview of Excel DMIN Function
Here, we will discuss the overview of the DMIN function. It is one of the Database Functions.
Summary
The DMIN function helps to determine the minimum value for a field/column based on user-specified criteria.
Syntax
=DMIN(database, field, criteria)
Arguments
| Argument | Required/Optional | Explanation |
|---|---|---|
| Database | Required | The database is the array, where the 1st row of the database identifies the name of the field. |
| Field | Required | It specifies the column to use in the function |
| Criteria | Required | Criteria hold the condition we apply |
Return Value
The DMIN function will give an output of a numerical value.
Version
The DMIN function was first introduced in Excel 2000, and it is available in every Excel version after that.
Note
- You can select any array for the criteria. However, to identify the condition, the array must include at least 1 column label and one cell below it.
- You should not place the criteria below the list.
- One thing must be remembered, the criteria should not overlap the list.
- To perform an operation on an entire column in a database, enter a blank line after the column labels.
DMIN Function in Excel: 4 Easy Examples
In the following dataset, you can see the Product, Store, Quantity, Unit Price, and Total Price columns. Further using this dataset, we will describe 4 easy and suitable examples for the DMIN function in Excel. Here, we used Excel 365. You can use any available Excel version.
1. Applying DMIN Function for Single Criteria
In this method, we will use the DMIN function for a single criterion.
Here, we want to find the Minimum Total for the product Laptop.
Therefore, you can see the criteria in cell B15:B16.
Steps:
- First of all, we will type the following formula in cell C18.
=DMIN(B4:F12,"Total Price",B15:B16)
Formula Breakdown
- B4:F12 is the database
- we typed “Total Price” for the Field.
- B15:B16 is the criteria.
- DMIN(B4:F12,”Total Price”,B15:B16) becomes
- Output: $600
- At this point, press ENTER.
- Hence, you can see the result in cell C18.
2. Use of Index Number As Field Argument in DMIN Function
Here, we will use an index for the Field in the DMIN function. The criteria are the same as in the previous example.
Steps:
- First, we will type the following formula in cell C18.
=DMIN(B4:F12,5, B15:B16)- Here, for the Field, we simply type the Index number 5.
- Then, press ENTER.
- As a result, you can see the output in cell C18.
3. Employing DMIN Function for Multiple Criteria
In this method, we will use the DMIN function for multiple criteria.
Here, we want to find the Minimum Total for the product Monitor, and the Quantity is 2.
Therefore, you can see the criteria in cell B15:C16.
Steps:
- First of all, we will type the following formula in cell C18.
=DMIN(B4:F12,"Total Price",B15:C16)
- Moreover, press ENTER.
- Therefore, you can see the result in cell C18.
4. Use of DMIN Function to Get a Result for Case-Sensitive Match
The DMIN function is not case-sensitive. To make the DMIN function case-sensitive, we will use the MATCH and EXACT functions in the DMIN function.
Here, we will find out the Minimum Unit Price, and you can see the multiple criteria in cells B15:C16.
Steps:
- First of all, we will type the following formula in cell C18.
=DMIN(B4:F12,MATCH(TRUE,EXACT("Unit Price",B4:F4),0),B15:C16)
Formula Breakdown
- EXACT(“Unit Price”, B4:F4) → the EXACT function checks if two text strings are identical, and returns TRUE or FALSE.
- Output: {FALSE, FALSE, FALSE, TRUE, FALSE}
- MATCH(TRUE, EXACT(“Unit Price”, B4:F4),0) → the MATCH function finds out a specific item in a range of cells.
- MATCH(TRUE,EXACT(“Unit Price”,B4:F4),0) → becomes
- MATCH(TRUE,{FALSE,FALSE,FALSE,TRUE,FALSE},0)
- Output: 4
- MATCH(TRUE,{FALSE,FALSE,FALSE,TRUE,FALSE},0)
- DMIN(B4:F12,MATCH(TRUE,EXACT(“Unit Price”,B4:F4),0),B15:C16) → becomes
- DMIN(B4:F12,4,B15,C16)
- Output: $250.
- DMIN(B4:F12,4,B15,C16)
- Later, press ENTER.
- Therefore, you can see the result in cell C18.
Practice Section
You can download the above Excel file and practice the explained methods.
Download Practice Workbook
You can download the Excel file and practice while reading this article.
Conclusion
Here, we show you 4 easy examples of using the DMIN function in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.
<< Go Back to Excel Functions | Learn Excel
