Excel MINIFS Function
MINIFS returns the smallest value whose matching cells satisfy one or more conditions.
Use it for conditional minimums, such as lowest bid by vendor type, earliest qualifying date, or smallest cost for one category and status.
MINIFS syntax & arguments
Syntax
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
-
1
min_range
RequiredThe cells to evaluate for the minimum when all criteria pass.
-
2
criteria_range1
RequiredThe first range of cells to test.
-
3
criteria1
RequiredThe condition that cells in
criteria_range1must meet. It can be a number, text, cell reference, expression such as">100", or wildcard pattern. -
4
criteria_range2, criteria2
Optional RepeatableAdditional range-and-criteria pairs. Each extra criteria range must line up with
min_range, and only values that meet every criterion are considered for the minimum.
Example
=MINIFS(D2:D50, B2:B50, "East", C2:C50, "Complete")
Return the smallest value in D2:D50 where B2:B50 is East and the matching cell in C2:C50 is Complete.
MINIFS caveats
-
The min range comes first
Like SUMIFS and AVERAGEIFS, MINIFS starts with the result range, then uses range-and-criteria pairs.
-
Criteria ranges must align
Every criteria range should cover the same rows or columns as
min_rangeso each test lines up with the value being compared. -
All conditions are AND logic
A value can become the minimum only when every criteria pair passes for the same row or position.
-
Operators usually need quotes
Criteria such as
">100"or"<>Cancelled"must be written as text. -
No matching numeric values can return 0
If no qualifying numeric value is found, MINIFS can return
0, which may look like a real minimum.
Need the highest matching value? Use MAXIFS when the same conditions should return a maximum instead.
Intro MINIFS practice problem
Solve the intro problem directly here, or open it on its own page.
Advanced MINIFS practice problems
Use MINIFS alongside other Excel functions in realistic, less-prescriptive challenges.
