MINIFS function
MINIFS returns the smallest value in a range, counting only the rows that meet every condition you set, such as the lowest price in one category. If no rows match it returns 0, which can look like a real minimum.
Example
Read it as a sentence: find the smallest Sales value in O2:O7 where the Region column in M2:M7 says North, which gives 100.
Syntax
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments in [square brackets] are optional.
-
min_range(required) - The cells to take the minimum from.
-
criteria_range1(required) - Range tested against the first condition; same size as min_range.
-
criteria1(required) - The condition, such as "b" or "<50".
-
criteria_range2, criteria2(optional) - Optional extra range and condition pairs; all must be met.
Returns: The smallest value in min_range among rows meeting every condition.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=MINIFS(Data!O2:O7,Data!M2:M7,"North") | 100 | One criterion |
=MINIFS(Data!O2:O7,Data!M2:M7,"North",Data!N2:N7,"B") | 200 | Two criteria |
=MINIFS(Data!O2:O7,Data!M2:M7,"West") | 0 | No match returns 0 |
=MINIFS(Data!O2:O7,Data!M2:M7,{"North";"South"}) | 100 | Array criteria spill |
=MINIFS(Data!O2:O7,Data!M2:M7,"*th") | 75 | Wildcard |
=MINIFS(Data!O2:O7,Data!M2:M5,"North") | #VALUE! | Mismatched sizes |
The Data sheet these formulas read
A cell that starts with = holds that formula.
Watch out for
- Mismatched range sizes give #VALUE!.
- Returns 0 if no rows match.
- Not in Excel 2016 or earlier perpetual versions.
- Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).
Where it works
- Excel
- MINIFS arrived in Excel 2019. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .