MINIFS function
MINIFS returns the smallest value among the rows that meet one or more conditions: the cheapest quote for a part, the earliest due date in a project, the lowest score above zero. Like MAXIFS, it returns 0 when no rows match, which can look like a real result.
- Available in
- Excel 2019 and later, including Microsoft 365
- Returns
- The smallest matching value, or 0 if no row meets the conditions.
- Google Sheets
- Also available in Google Sheets.
Syntax
=MINIFS(min_range, criteria_range1, criteria1, ...)
| Argument | Required? | What it is |
|---|---|---|
min_range | Required | The cells to find the smallest value in. |
criteria_range1 | Required | The first range to check. |
criteria1 | Required | The condition for the first range. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| G2 | =MINIFS(C2:C8,B2:B8,"Bolt") | $0.37 | The lowest price quoted for bolts |
| G3 | =MINIFS(C2:C8,B2:B8,"Bolt",D2:D8,"<=5") | $0.39 | Cheapest bolt that can arrive within 5 days |
| G4 | =MINIFS(C2:C8,C2:C8,">0") | $0.05 | Skips the $0 placeholder quote |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Quote | Part | Unit price | Lead days | Check | Result | |
| Q-1 | Bolt | $0.42 | 6 | Cheapest bolt | $0.37 | |
| Q-2 | Nut | $0.18 | 3 | Cheapest bolt in 5 days | $0.39 | |
| Q-3 | Bolt | $0.37 | 9 | Lowest price above zero | $0.05 | |
| Q-4 | Washer | $0.05 | 2 | |||
| Q-5 | Bolt | $0.45 | 4 | |||
| Q-6 | Nut | $0.00 | 5 | |||
| Q-7 | Bolt | $0.39 | 5 |
Practice MINIFS
Graded instantly, then re-checked on changed data.
Best price per supplier
G2, return the lowest unit price offered by the supplier in F2. Fill down to G4.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| A 0 that should be "no data" | MINIFS returns 0 when nothing matches. Check the count with COUNTIFS before trusting a 0. |
| #NAME? | MINIFS needs Excel 2019 or later. In older versions use =MIN(IF(B2:B8="Bolt",C2:C8)) as an array formula. |
| Placeholder zeros win | A 0 entered for "unknown" becomes the minimum. Add a condition like ">0" on the min range. |
Tips
- MINIFS on a date column returns the earliest matching date, such as the next deadline for a project.
Questions
How do I find the minimum value with a condition?
Use =MINIFS(C2:C8,B2:B8,"Bolt") in Excel 2019 or later.
How do I find the smallest value greater than zero?
Use =MINIFS(C2:C8,C2:C8,">0").
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.