Minimum Value with Criteria (MINIFS)

Excel Formulas › Min & Max

Excel 2019+MINIFSArray alt

The mirror of MAXIFS: MINIFS returns the smallest value that meets a condition — the cheapest product in a category, the lowest time per group — without an array formula.

Quick formula: for the min of column C where region (B) equals E2:
=MINIFS(C2:C8, B2:B8, E2)
Same shape as MAXIFS: min range first, then range, criteria pairs.

Functions used (tap for the full reference guide):

MINIFSMIN

The example

Orders by region. We want the smallest West order.

ABCEF
1RepRegionAmountRegionMin
2AnaWest$120West$120
3BenEast$50
4CyWest$180
5DotEast$70
6EveWest$150

The formula

The smallest West order — East’s $50 is ignored:

=MINIFS(C2:C6, B2:B6, "West") // min of {120,180,150} = 120

How it works

MINIFS filters, then takes the minimum:

  1. The first argument, C2:C6, is the range to take the minimum from.
  2. The pair B2:B6, "West" restricts to West rows.
  3. MINIFS returns the smallest matching value — 120 — not East’s lower $50.
  4. Add more conditions with extra range, criteria pairs.

Try it: interactive demo

Live demo

Pick a region; see the smallest order in it.

Min:

Variations

Min above zero (ignore blanks/zeros)

Smallest positive value:

=MINIFS(C2:C6, C2:C6, ">0")

Min with two conditions

Cheapest West Widget:

=MINIFS(C2:C6, B2:B6, "West", D2:D6, "Widget")

Legacy array version

For Excel 2016 and older (Ctrl+Shift+Enter):

=MIN(IF(B2:B6="West", C2:C6))

Pitfalls & errors

MINIFS needs Excel 2019+. Older versions show #NAME? — use MIN(IF()) array-entered.

Returns 0 when nothing matches — and also if 0 is genuinely the smallest. If blanks sneak in as zeros, add a ">0" condition.

Mismatched range sizes give #VALUE!. Keep the min range and criteria ranges identical in size.

Practice workbook

📊
Download the free Minimum Value with Criteria (MINIFS) practice workbook
The orders table with MINIFS (result shown live), the positive-only and two-condition versions, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find the minimum value with a condition in Excel?
Use =MINIFS(min_range, criteria_range, criteria), e.g. =MINIFS(C2:C8, B2:B8, "West") returns the smallest West value. Available in Excel 2019 and later.
How do I ignore zeros in MINIFS?
Add a condition on the value range itself: =MINIFS(C2:C6, C2:C6, ">0") returns the smallest value greater than zero.
How do I do MINIFS in older Excel?
Use =MIN(IF(criteria_range=criteria, min_range)) entered with Ctrl+Shift+Enter as an array formula.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Maximum value with criteria · Average by group

Function references: MINIFS · MIN