MINIFS in Excel: Syntax, 5 Examples, Fallback for Older Excel | Sheets & Cells
Function · Math & Statistical

MINIFS — Find the Smallest Value with Multiple Criteria

The fifth and final piece of the IFS family. Where MAXIFS surfaces the biggest, MINIFS finds the floor: smallest deal per segment, cheapest supplier per part, lowest inventory level per SKU. Essential for reorder points, threshold monitoring, worst-case analysis, and finding your bottom performers. Excel 2019+.

⚠️
Requires Excel 2019, Excel 365, or Excel for the web
MINIFS shipped alongside MAXIFS in Excel 2019 — later than the rest of the IFS family. Excel 2016 and earlier show a NAME error. For those users, the MIN(IF()) array formula fallback is documented below.
Quick answer
MINIFS returns the smallest number in a range that meets one or more criteria. Each row is considered only if ALL criteria match (AND logic). Structurally identical to MAXIFS — same argument pattern, opposite aggregation.
Syntax
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
Working example
=MINIFS(Amount, Region, "North", Product, "Widget A") → Returns the SMALLEST Amount where Region = "North" AND Product = "Widget A". With our sample data: $2,499.75 — Emma Thompson's Jan 3 transaction.
✓
Universal support since Excel 2019
Excel 2019, 2021, 365, Excel for the web, Google Sheets, LibreOffice, Apple Numbers. Identical behavior everywhere. Also see the MAXIFS twin sibling — same syntax, opposite direction.
📗 Free MAXIFS & MINIFS example workbook
9 sheets · same 24-row sales log as SUMIFS/COUNTIFS/AVERAGEIFS · MAXIFS + MINIFS side-by-side · unique Full IFS Family Reconciliation sheet
Download .xlsx (free) Open in Sheets
Category
Math & Statistical
Difficulty
Beginner → Intermediate
Excel version
2019+
Max criteria
127 pairs
127
Max criteria pairs
5
Worked examples
5th
IFS family piece
8/10
Templates using it

Syntax breakdown

MINIFS has five arguments to start (three at minimum), then any number of additional criteria pairs. Identical structure to MAXIFS with min_range as the first argument.

ArgumentTypeWhat it does
min_range REQUIRED The column to search for the minimum value. First argument. Non-numeric cells are ignored (not treated as 0).
criteria_range1 REQUIRED First column to filter on. Must be the same size as min_range.
criteria1 REQUIRED Value or expression to match. Text ("North"), number, cell reference, or comparison (">100").
criteria_range2 OPTIONAL Second filter column. Add up to 126 more pairs (127 total).
criteria2 OPTIONAL Second criteria expression. All criteria must be true for a row to be considered.
The zero-match gotcha — even more subtle for MINIFS: MINIFS returns 0 when no rows match. This is especially treacherous because 0 LOOKS like a valid "smallest value" — it's the smallest non-negative number. If your data can legitimately contain 0 (a discount, a refund, a zero balance), you cannot distinguish "no matches" from "matched a row with value 0" without a COUNTIFS check.

Five working examples

Every example uses the same 24-row sales log as the other IFS functions. Grand min = $1,300 (Aisha's March Widget B), grand max = $13,350.

01 Basic MINIFS — smallest deal per region

Find the smallest single transaction in each region. Reveals the floor — where deals shrink to.

RegionCountSmallest deal
North8$2,249.55
South6$1,499.70
East5$1,625.00
West5$1,300.00
=MINIFS(Amount, Region, "West")
→ Returns $1,300.00 — the smallest deal all quarter (Aisha's Widget B, March)

The insight: while North has the biggest ceiling (highest MAXIFS), it also has the highest floor (smallest deal is still $2.2K). West has a lower floor AND lower ceiling — its whole distribution sits below. This is territory-quality signal you can't get from totals alone.

02 Multiple criteria — smallest deal per segment

Find the smallest transaction in a specific segment. Layer criteria with AND logic.

=MINIFS(Amount, Region, "North", Product, "Widget A")
→ Returns $2,499.75 — smallest North+Widget A deal (Emma, Jan 3)
=MINIFS(Amount, Region, "North", Product, "Gizmo Pro")
→ Returns $11,570.00 — even the smallest Gizmo Pro sale in North was substantial
=MINIFS(Amount, Salesperson, "Aisha Patel")
→ Returns $1,300.00 — Aisha's smallest deal (also the quarter's minimum)
🔗 The full IFS family reconciles
For North + Widget A: SUMIFS=$6,249.60, COUNTIFS=2, AVERAGEIFS=$3,124.80, MAXIFS=$3,749.85, MINIFS=$2,499.75. Identity check for 2-row segments: MAXIFS + MINIFS = SUMIFS → $3,749.85 + $2,499.75 = $6,249.60 ✓. All five functions agree.

03 Date range — smallest deal per month

Track the floor over time. Reveals when your smallest deals hit new lows.

MonthCountSmallest
Jan 20268$1,625.00
Feb 20268$1,499.70
Mar 20268$1,300.00
=MINIFS(Amount, Date, ">="&DATE(2026,3,1), Date, "<="&DATE(2026,3,31))
→ Returns $1,300.00 — March hit a new quarterly low
The floor keeps dropping: Jan floor $1,625 → Feb $1,499 → Mar $1,300. Three months of shrinking bottom deals — a warning sign that either discounting is creeping in or entry-level segments are eroding. Only MINIFS surfaces this trend. AVERAGEIFS smooths it away.
=MINIFS(Amount, Date, ">="&EOMONTH(TODAY(),-1)+1, Date, "<="&EOMONTH(TODAY(),0))
→ Smallest deal in the current month, self-updating.

04 Wildcards — smallest per product family

Find the smallest deal in a grouped product family.

=MINIFS(Amount, Product, "Widget*")
→ Returns $1,300.00 — smallest Widget-anything deal
=MINIFS(Amount, Product, "Gizmo*")
→ Returns $1,499.70 — even the smallest Gizmo held $1.5K
=MINIFS(Amount, Product, "Bundle*")
→ Returns $6,450.00 — bundles have a much higher floor than singletons

The takeaway: floors vary wildly by product family. Bundle products can't go below $6.4K; singleton widgets can drop to $1.3K. Pricing floors are structural, and MINIFS surfaces them.

05 Comparison operators — smallest within a bucket

Find the smallest deal above a threshold. Useful for tier analysis, minimum-order enforcement, or SLA compliance.

=MINIFS(Amount, Amount, ">5000")
→ Returns $6,450.00 — smallest deal in the >$5K bucket (the "just barely made it" deal)
=MINIFS(Amount, Amount, ">2000", Region, "West")
→ Returns $2,275.00 — smallest West deal that cleared $2K
=MINIFS(Amount, Amount, ">"&$C$22)
→ Dynamic threshold from cell — updates live
The threshold-monitoring pattern: MINIFS with a > comparison surfaces the "smallest thing that met the bar." Perfect for SLA monitoring ("smallest response time that exceeded 24 hours"), quality gates ("smallest score that passed"), or minimum-order compliance.

Interactive playground

Try it Live MINIFS demonstration

Mirrors live cells from the workbook. Change the yellow inputs → the blue answer updates.

Input · Region
West
Output · Smallest deal
$1,300.00
=MINIFS(XAmount, XRegion, "West")
Input · Region + Product
North + Widget A
Output · Smallest deal
$2,499.75
=MINIFS(XAmount, XRegion, $C$24, XProduct, $C$25)

Download the workbook to try the live dropdown filters and see MINIFS alongside MAXIFS in every example.

Common errors and how to fix them

Six common MINIFS scenarios — nearly identical to MAXIFS since they're twin functions:

ResultWhy it happensBroken → Fix
NAME error You're on Excel 2016 or earlier. MINIFS didn't exist until Excel 2019. MINIFS(Amount, Region, "North") // NAME error {=MIN(IF(Region="North", Amount))} // Ctrl+Shift+Enter
0 (silent) No rows match the criteria — MINIFS returns 0 rather than an error. Also matches if a real row has value 0. MINIFS(Amount, Region, "Nowhere") // returns 0 =IF(COUNTIFS(Region,"Nowhere")=0, "No data", MINIFS(Amount, Region, "Nowhere"))
VALUE error min_range and criteria_range are different sizes. MINIFS(G6:G29, C6:C15, "North") MINIFS(G6:G29, C6:C29, "North")
Wrong min Missing & concatenation causing zero matches on a comparison. MINIFS(Amount, Amount, ">C2") // literal text MINIFS(Amount, Amount, ">"&C2)
0 (silent) Trailing spaces in criteria or data (invisible but breaks match). MINIFS(Amount, Region, "North ") MINIFS(Amount, Region, TRIM("North "))
VALUE error Reference to a closed workbook (MINIFS cannot read closed external files). MINIFS('[Closed.xlsx]Sheet1'!A:A, ...) Open the source workbook, or import via Power Query

Fallback for Excel 2016 and earlier

Stuck on Excel 2007-2016? MINIFS isn't there. Use a legacy MIN(IF()) array formula:

{=MIN(IF(Region="North", Amount))} ' Single criterion
{=MIN(IF((Region="North")*(Product="Widget A"), Amount))} ' Multiple criteria (AND)
Two gotchas with the fallback: (1) Enter with Ctrl+Shift+Enter, not Enter. (2) The IF() returns FALSE (which equals 0) for non-matching rows, and MIN will pick 0 as the minimum. Fix by returning a large number for non-matches: {=MIN(IF((Region="North"), Amount, 9^99))}. This is a MIN-specific quirk — MAXIFS doesn't have it because FALSE=0 doesn't beat real values.
📗 Every example above, in one workbook
Same 24-row log as the whole IFS family · MAXIFS/MINIFS side-by-side · Spread pattern · Full IFS reconciliation.
Download maxifs-minifs-examples-2026.xlsx

The full IFS family, reconciled

MINIFS completes the IFS family. Combined with its four siblings, you get a full statistical picture of any segment — the killer dashboard view:

All 5 IFS functions on the same filters

SUMIFS answers "how much?" · COUNTIFS answers "how many?" · AVERAGEIFS answers "what size each?" · MAXIFS answers "biggest?" · MINIFS answers "smallest?" — together they describe any segment completely.

Filter SUMIFS COUNTIFS AVERAGEIFS MAXIFS MINIFS
By region
North$51,3948$6,424.27$13,350.00$2,249.55
South$27,6486$4,608.02$9,250.00$1,499.70
East$31,6005$6,319.95$10,680.00$1,625.00
West$14,8005$2,959.93$7,525.00$1,300.00
Multi-criteria
North + Widget A$6,2502$3,124.80$3,749.85$2,499.75
North + Gizmo Pro$24,9202$12,460.00$13,350.00$11,570.00
South + Widget A$4,9992$2,499.50$3,124.75$1,874.25
The identity check: for any 2-row segment, MAXIFS + MINIFS = SUMIFS. Verify: North+Widget A → $3,749.85 + $2,499.75 = $6,249.60 ✓. If your dashboard's five IFS numbers don't reconcile, one of the formulas has a bug — mismatched criteria, hardcoded filter, or divergent named range.

The MIN function's unique warning signal

MINIFS surfaces the floor — the value beneath which nothing dropped. Watch the trend of MINIFS over time:

  • Floor stable → Pricing discipline holding. Good.
  • Floor rising → Deals shrinking to zero. Segment maturing or premium-only bias emerging.
  • Floor dropping → Discounting creeping in, or entry-tier eroding. Investigate.

MINIFS is often more informative than AVERAGEIFS for early-warning signals because averages smooth outliers away. The floor doesn't hide.

The IFS family — five functions, one pattern

MINIFS is the fifth and final member of the IFS family. All five share the same criteria model:

Meet the family

Every function below uses the same (aggregate_range, criteria_range1, criteria1, ...) pattern. Only the aggregation verb changes.

SUMIFS, COUNTIFS, and AVERAGEIFS ship with Excel 2007+. MAXIFS and MINIFS require Excel 2019 or 365.

Related functions

Excel version compatibility

MINIFS shipped alongside MAXIFS in Excel 2019 — same version support:

PlatformSupports MINIFS?Notes
Excel 365 (Windows & Mac)✓ YesFull support
Excel 2021✓ YesFull support
Excel 2019✓ YesIntroduced here
Excel 2016 / 2013 / 2010 / 2007✗ NoUse MIN(IF()) array formula
Excel 2003 and earlier✗ NoUse MIN(IF()) array formula
Excel for the web✓ YesFull support
Excel on iPad & iPhone✓ YesFull support
Google Sheets✓ YesSame syntax, same behavior
LibreOffice Calc✓ YesVersion 5.2+
Apple Numbers✓ YesSame syntax

When to use MINIFS vs. alternatives

Use MINIFS when…

  • You need the smallest value in a filtered subset. Cheapest supplier per part, smallest deal per rep, lowest inventory per SKU.
  • You're monitoring thresholds. "What's the smallest value that met the SLA?"
  • You're looking for floors, discounting, or worst-case values.
  • You're on Excel 2019+. Otherwise, use MIN(IF()) array formula.

Use MIN instead when…

  • You want the smallest value in a whole range with no filters. =MIN(A2:A1000).

Use SMALL instead when…

  • You want the 2nd, 3rd, or Nth smallest — not just the bottom. =SMALL(range, 2) gives you the second-smallest value.

Use MIN(IF()) array formula when…

  • You're on Excel 2016 or earlier where MINIFS doesn't exist.
  • You need OR logic across criteria (MINIFS is AND-only).

How MINIFS actually works

The algorithm

MINIFS walks through each row of your criteria_ranges in parallel. For every row, it checks: do ALL criteria pairs match? If YES, compare that row's min_range value to a running minimum. At the end, return the smallest value it saw. If no rows matched, return 0.

Text criteria — case and whitespace

  • Case-insensitive: "NORTH", "north", and "North" all match.
  • Wildcards: * for any characters, ? for one.
  • Whitespace is significant — trailing spaces silently break matches. Use TRIM().

Numeric criteria — operators inside quotes

  • Operators go INSIDE the criteria string: ">100", not >100.
  • Cell references combine with &: ">"&C2.

Non-numeric cells in min_range

Empty cells, text values, and errors in the min_range are IGNORED — they don't participate in the min calculation. If ALL matching rows are non-numeric, MINIFS returns 0.

Performance notes

MINIFS is fast — comparable to MAXIFS and SUMIFS. Three drivers:

  • Range size. Narrow ranges beat whole-column references.
  • Number of criteria pairs. Each pair is another per-row comparison.
  • Data-type consistency. Mixed types force Excel to coerce on every check.

How to write a MINIFS from scratch

  1. Check your Excel version

    MINIFS needs Excel 2019 or later. Older versions get the NAME error — jump to the MIN(IF()) array fallback.

  2. Identify the min_range

    What column has the numbers you want the minimum from? That's your FIRST argument.

  3. Write your first criteria pair

    criteria_range + criteria. Simple text and numbers work as-is; comparisons go in quotes.

  4. Add more criteria as needed

    Each additional filter is another range/criteria pair. All must match — AND logic. Up to 127 pairs.

  5. Guard against the zero-match trap

    MINIFS returns 0 on no matches — indistinguishable from a real 0. Use =IF(COUNTIFS(...)=0, "No data", MINIFS(...)) when 0 could be legitimate.

Functions used with MINIFS

🎁 Grab the free MAXIFS & MINIFS workbook
Same 24-row sales log · MAXIFS/MINIFS side-by-side · The Spread pattern · Full IFS Family Reconciliation.
Download maxifs-minifs-examples-2026.xlsx

Frequently asked questions

Why does MINIFS give a NAME error?

You're on Excel 2016 or earlier. MINIFS was introduced in Excel 2019 alongside MAXIFS. Upgrade to Excel 2019/365, or use the {=MIN(IF(...))} array formula fallback shown above.

What's the difference between MIN and MINIFS?

MIN takes a single range and returns the smallest value — no filters. MINIFS adds one or more filter conditions and returns the smallest value in matching rows. If you don't need filters, MIN is simpler and slightly faster.

How do I get the 2nd smallest instead of the smallest?

Use SMALL with an array formula: {=SMALL(IF(Region="North", Amount), 2)} returns the 2nd-smallest North transaction. MINIFS only returns the very bottom.

Why is my MINIFS returning 0?

Two possibilities: (1) no rows match your criteria, or (2) a matching row genuinely has value 0. To distinguish, run =COUNTIFS(...) with the same criteria — if COUNTIFS returns 0, you have no matches; if it's positive, one of the matches truly is 0.

Can MINIFS handle OR logic?

No — MINIFS is AND-only. For OR logic, use the array formula fallback: {=MIN(IF((Region="North")+(Region="South"), Amount, 9^99))}. Note the 9^99 for non-matches — otherwise MIN would pick FALSE=0 as the minimum.

How do I find the smallest deal in a date range?

Same pattern as MAXIFS — two criteria on the Date column: =MINIFS(Amount, Date, ">="&DATE(2026,1,1), Date, "<="&DATE(2026,3,31)).

Does MINIFS ignore negative numbers?

No — MINIFS includes negative numbers in the comparison. If your data has negatives (returns, debts, losses), the smallest value will be the most negative one. Use MINIFS(Amount, Amount, ">=0") to restrict to non-negatives.

Does MINIFS work in Google Sheets?

Yes, with identical syntax. Google Sheets has supported MINIFS since 2018.

Is MINIFS case-sensitive?

No — MINIFS is case-insensitive for text comparisons. For case-sensitive filtering, use MIN(IF()) with EXACT.

What if my min_range has text or errors?

Non-numeric cells in min_range are IGNORED. MINIFS won't crash on stray strings or errors — it skips them. If ALL matching rows are non-numeric, MINIFS returns 0.

How does MINIFS reconcile with the other IFS functions?

For any 2-row segment, MAXIFS + MINIFS = SUMIFS. Example: South+Widget A has 2 transactions — MAXIFS = $3,124.75, MINIFS = $1,874.25, sum = $4,999.00 = SUMIFS. This is the family identity that verifies your dashboard formulas agree.

Which templates use MINIFS?

The same 8 templates that use MAXIFS — minimum values complete the range story. Budget Tracker (lowest monthly spend), Expense Report (smallest reimbursement), Inventory Tracker (lowest stock), Sales Dashboard (smallest deal per rep), KPI Dashboard, Employee Attendance, Timesheet (minimum weekly hours), and Project Timeline (shortest task).

Templates that use MINIFS

The same 8 of 10 templates that use the rest of the IFS family — MIN values complete the range story:

Skip the syntax. Ask in plain English.

The Sheets & Cells AI Add-in writes MINIFS, MAXIFS, and the whole IFS family — plus the MIN(IF()) fallback for older Excel — right inside Excel. Type "smallest Q1 deal for Widget A in West" and get the working formula, ready to paste.

Try the AI Add-in →