# MINIFS function in Excel

> 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.

Source: https://spreadsheetpractice.com/functions/minifs · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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)
- **Available in:** Excel 2019 and later, including Microsoft 365
- **Category:** [Conditional sum and count functions](https://spreadsheetpractice.com/functions/category/conditional)
- **Returns:** The smallest matching value, or 0 if no row meets the conditions.
- **Google Sheets:** Also available in Google Sheets.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Quote | Part | Unit price | Lead days |  | Check | Result |
| 2 | Q-1 | Bolt | $0.42 | 6 |  | Cheapest bolt | $0.37 |
| 3 | Q-2 | Nut | $0.18 | 3 |  | Cheapest bolt in 5 days | $0.39 |
| 4 | Q-3 | Bolt | $0.37 | 9 |  | Lowest price above zero | $0.05 |
| 5 | Q-4 | Washer | $0.05 | 2 |  |  |  |
| 6 | Q-5 | Bolt | $0.45 | 4 |  |  |  |
| 7 | Q-6 | Nut | $0.00 | 5 |  |  |  |
| 8 | Q-7 | Bolt | $0.39 | 5 |  |  |  |

## 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.

## Related functions

- [MAXIFS](https://spreadsheetpractice.com/functions/maxifs): MAXIFS returns the largest value among the rows that meet one or more conditions: the biggest deal in a region, the latest order date for a customer.
- [MIN](https://spreadsheetpractice.com/functions/min): MIN returns the smallest number from a list of values or ranges.
- [SMALL](https://spreadsheetpractice.com/functions/small): SMALL returns the k-th smallest value in a range: k = 1 is the smallest, 2 the second smallest, and so on.

## 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").

## Practice

A graded MINIFS exercise ("Best price per supplier") is on [the MINIFS page](https://spreadsheetpractice.com/functions/minifs). It is checked instantly, then re-checked on changed data.

Lessons that use MINIFS:

- [Lesson 38: MAXIFS & MINIFS by group](https://spreadsheetpractice.com/learn/maxifs-minifs-by-group) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
