Home Excel How to Use the DMIN Function in Excel (Formula, Usage, Examples)

How to Use the DMIN Function in Excel (Formula, Usage, Examples)

by Kim Thu
A+A-
Reset

The DMIN function in Excel is a database function used to find the smallest value in a column that meets specified criteria. This function is particularly useful when you need to identify the lowest value in a list while ensuring that specific conditions are met.

1. Syntax of the DMIN Function

The formula for the DMIN function is:

DMIN(database, field, criteria)

Explanation of Arguments:

  • database: The main data table, including headers and data.
  • field: The column name or position number (enclosed in quotes "" or entered as a number).
  • criteria: The range that contains the filtering conditions.

2. Practical Example

Problem Statement

Suppose we have the following sales data table:

Product Category Quantity Revenue (VND)
Milk Food 50 2,500,000
Cake Food 30 1,200,000
Pants Fashion 20 3,500,000
Shirt Fashion 40 2,800,000
Sandals Footwear 15 1,000,000
Shoes Footwear 25 2,300,000

Now, let’s find the smallest quantity in the “Food” category.

Creating the Criteria Range

You need to create a criteria table like this:

Category
Food

Formula Implementation

Assuming the main data is in A1:D7 and the criteria range is in F1:F2, the formula would be:

=DMIN(A1:D7, "Quantity", F1:F2)

Result: 30 (since “Cake” has the smallest quantity in the “Food” category).

How to Use the DMIN Function in Excel 1

4. Common Errors and Fixes

Error Cause Solution
#VALUE! Incorrect or misspelled field name Check column name spelling
#NUM! Empty database or no matching results Verify criteria range
#NAME? Incorrect function name Ensure correct function syntax

5. Comparison: DMIN vs. MINIFS

What Is MINIFS?

The MINIFS function was introduced in Excel 2016. It finds the smallest value within a range based on given conditions, similar to DMIN but without requiring a structured database table.

MINIFS formula syntax:

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Key Differences Between DMIN and MINIFS

Criteria DMIN MINIFS
Supports multiple criteria Yes Yes
Filtering method Based on a criteria range Direct condition input
Data structure required Database-style table Flexible range
Excel versions Excel 2003+ Excel 2016+
Formula complexity Requires a clear criteria range Shorter, more intuitive

When to Use DMIN vs. MINIFS?

  • Use DMIN when dealing with large database tables and applying flexible filtering criteria.
  • Use MINIFS for quick calculations on a standard data range without setting up a criteria table.

Example Use Cases:

  • DMIN is ideal for managing large datasets with dynamic criteria.
  • MINIFS is better for simple filtering tasks without the need for a separate criteria range.

6. Conclusion

The DMIN function is an excellent tool for handling large datasets and flexible filtering criteria in Excel. Use DMIN when you need advanced filtering that MINIFS cannot handle!

Happy Excel-ing!

See more: How to use the AGGREGATE function in Excel Detailed and fast

Related Posts

Leave a Comment