The MINIFS function was officially introduced with Excel 2019, but thanks to Modern Functions Update (MFU) you can now run it on your Excel 2007, 2010, 2013 and 2016 versions. It works exactly as its original Microsoft counterpart.
Description
The MINIFS function returns the largest numeric value in a range of cells that meet one or more specified conditions. It is the conditional counterpart to the MIN function, similar to how SUMIFS relates to SUM.
This is particularly useful for:
- Finding top performers: Get the highest sales figure for a specific product or region
- Data analysis: Find minimum values that meet multiple criteria simultaneously
- Quality control: Identify the largest value within a specific category
- Reporting: Create dynamic summaries based on conditions
Syntax
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
| Argument | Description |
|---|---|
min_range | Required. The range of cells from which you want to find the minimum value. |
criteria_range1 | Required. The first range to evaluate against the condition. |
criteria1 | Required. The condition to apply to criteria_range1. |
criteria_range2, criteria2, ... | Optional. Additional ranges and their associated conditions. Up to 126 range/criteria pairs are supported. |
Important Rules
- All
criteria_rangearguments must be the same size asmin_range, otherwiseMINIFSreturns a#VALUE!error. - If no cells meet the criteria,
MINIFSreturns 0 (zero). - Empty cells that meet criteria are automatically ignored.
MINIFSrequires actual ranges – you cannot substitute arrays.
Criteria Syntax
The MINIFS function supports logical operators (>, <, <>, =) and wildcards (*, ?, ~) for partial matching. When using operators, they must be enclosed in double quotes.
| Target | Criteria |
|---|---|
| Cells greater than 75 | ">75" |
| Cells equal to 100 | 100 or "100" |
| Cells less than or equal to 100 | "<=100" |
| Cells equal to “Red” | "red" |
| Cells not equal to “Red” | "<>red" |
| Cells that begin with “X” | "x*" |
| Cells less than value in A1 | "<"&A1 |
| Cells less than today | "<"&TODAY() |
When using a value from another cell, the cell reference must be concatenated to the operator using the ampersand (&).
Examples
Basic Usage – Single Condition
If you have sales data and want to find the minimum sale amount for “USA”:
=MINIFS(E2:E100, C2:C100, "USA")
This returns the highest value in E2:E100 where the corresponding cell in C2:C100 equals “USA”.
Multiple Conditions
To find the minimum Amount for Student under 18:
=MINIFS(D:D,E:E,"Student",C:C,"<18")
Please see the table and formula:
Using Values from Other Cells
To find the minimum value in A1:A100 where cells in B1:B100 are greater than the value in C1:
=MINIFS(A1:A100, B1:B100, ">"&C1)
The concatenation ("<"&C1) is required because Excel needs to evaluate the cell reference first.
Using Wildcards
To find the minimum value where cells in B1:B100 begin with “a”:
=MINIFS(A1:A100, B1:B100, "a*")
The asterisk (*) matches any number of characters.
Practical Use Cases
MINIFS vs MIN + IF (Array Formula)
In older Excel versions without MINIFS, you would use an array formula based on MIN and IF:
{=MIN(IF(criteria_range1=criteria1, min_range))}
Example: Find minimum value in A1:A100 where B1:B100 equals “X”:
{=MIN(IF(B1:B100="X", A1:A100))}
Note: This is an array formula and must be entered with Ctrl+Shift+Enter.
Advantages of MINIFS:
- ✅ Simpler and more readable syntax
- ✅ No Ctrl+Shift+Enter required
- ✅ Multiple conditions are easier to add
- ✅ Works natively with references
Notes
- ✅ Fully tested – The implementation in Modern Functions Update works identically to Microsoft’s original function introduced in Excel 2019.
- ✅ The function is fully compatible with Excel 2007, 2010, and all later versions.
- ℹ️
MINIFScan handle up to 126 range/criteria pairs. - ℹ️ If no cells match the criteria,
MINIFSreturns0(zero) – be careful if0is a valid value in your data. - ⚠️
MINIFSrequires actual ranges, not arrays. - ⚠️ All criteria ranges must be the same size as
min_range.