MINIFS function
Quick answer
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.
- Syntax
=MINIFS(min_range, criteria_range1, criteria1, ...)- Most common use
=MINIFS(C2:C8,B2:B8,"Bolt")returns $0.37 (The lowest price quoted for bolts)
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").
Lessons that use MINIFS
Answers that use MINIFS
In other languages
MINIFS is MIN.SI.CONJUNTO in Spanish Excel and MINWENNS in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .