If you have a set of data and a set of criteria, the DMIN function can retrieve the minimum value from the given data and criteria.
How to use DMIN
=DMIN(data, field, criteria)
- Data – Data range with headers
- Field – Name or index of the field containing the minimum value
- Criteria – Criteria range with headers
Make sure the Data and Criteria have similar headers. DMIN processes the data in the same header and finds the minimum from the Data matching the Criteria.
In the figure below is a set of items with the price, amount, and destination. We can find the minimum price of item C with the “amount” greater than 2 as follows:
- Have the Data and Criteria in the same sheet
- Select a cell and insert the Syntax
- Press Enter
In this Dataset we are using is D4:G10, the minimum field is Price or 2 as an index, and the Criteria is J4:M5. From here, DMIN finds C under the header Item, retrieves the minimum value greater than 2 under header Amount and neglects header Destination because we leave it blank in the Criteria. The result is 35 from cell E9.
Figure 1. How to use DMIN
- Using Wildcards in Criteria
These are some wildcards used for shortening syntax that can be used in the Criteria space. The table below shows some of them.
The usage range of the Wildcards is not as extensive in DMIN as in other functions, like COUNTIF. You should thoroughly test the Wildcards first before start using them.
The Criteria can have more than 1 row below the headers. If you choose to have more than 1 row below the headers, please keep in mind that in each row, DMIN joins the criteria with AND logic; and the criteria in all rows will be joined with OR logic.
In the picture below, if you use 2 or more rows for Criteria, DMIN will evaluate the minimum of either item C with more than 2 amount or item A with Destination to Berlin.
Figure 2. Multi-row Criteria
In short, take these notes when using DMIN:
- The Data and Criteria must have similar headers
- The column in Field can be expressed as text in “” or an index
- Some of the Wildcards are available in Criteria
- Criteria can have multi-row