Conditional function
Excel MINIFS function
MINIFS returns the smallest number in a range, counting only the rows that meet your conditions: the cheapest quote for an item, the fastest time in one group, the earliest date for a customer. It works like SUMIFS, with MIN in place of SUM.
=MINIFS(min_range, criteria_range1, criteria1, ...)Arguments
min_range- The numbers to take the smallest from.
criteria_range1- The first range to test. It must be the same size as min_range.
criteria1- The condition, such as "Paper", "<=5" or ">0".
MINIFS examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Supplier | Item | Quote | Lead days |
| 2 | Alder Supply | Paper | 41.5 | 3 |
| 3 | Brightline | Paper | 39.9 | 6 |
| 4 | Alder Supply | Toner | 88 | 2 |
| 5 | Corvex | Toner | 79.5 | 9 |
| 6 | Brightline | Toner | 84 | 4 |
| 7 | Corvex | Paper | 43 | 1 |
| 8 | Dunmore | Toner | 0 | 7 |
Download this sheet as an Excel practice file (.xlsx)
Result: 39.9
The cheapest paper quote: 39.9 from Brightline.
Result: 84
Cheapest toner that arrives within 5 days: 84.
Result: 0
Dunmore has not quoted yet and shows 0, so the plain minimum for toner is 0.
Result: 79.5
Add a second condition on the quote itself to skip the zero: 79.5.
How to use MINIFS
- Click the cell for the result.
- Type =MINIFS( and select the numbers to compare.
- Add a comma, the range to test, a comma and the condition.
- Add more range, condition pairs if needed, close the bracket and press Enter.
Common MINIFS mistakes
Zeros that mean missing
A 0 placeholder wins every minimum. Add ">0" on the same range as a condition, as in the last example. Empty cells are skipped already.
Reading 0 as a real result
With no matching rows MINIFS returns 0. Check with COUNTIFS first if 0 could also be a real value.
Ranges of different sizes
min_range and every criteria range must be the same size, or MINIFS returns #VALUE!.
MINIFS FAQ
How do I find the minimum value with criteria in Excel?
Use =MINIFS(C2:C8,B2:B8,"Paper"): the smallest value in C2:C8 where column B says Paper.
How do I find the minimum excluding zeros?
Use the value range as its own criteria range: =MINIFS(C2:C8,C2:C8,">0"). Add more conditions after it.
Why does MINIFS return 0?
Either no row meets every condition, or a matching row holds 0. Check the conditions for typos and extra spaces, and exclude zeros with ">0".
Does MINIFS work in Excel 2016?
No, it needs Excel 2019 or later, Microsoft 365 or Excel for the web. In Excel 2016 use =MIN(IF(B2:B8="Paper",C2:C8)) with Ctrl+Shift+Enter.
Related functions
MAXIFSLargest value that matchesMINSmallest numberAVERAGEIFSAverages with several conditionsCOUNTIFSCounts with several conditions
Updated