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+.
MIN(IF()) array formula fallback is documented below.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.
| Argument | Type | What 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. |
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.
| Region | Count | Smallest deal |
|---|---|---|
| North | 8 | $2,249.55 |
| South | 6 | $1,499.70 |
| East | 5 | $1,625.00 |
| West | 5 | $1,300.00 |
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.
$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.
| Month | Count | Smallest |
|---|---|---|
| Jan 2026 | 8 | $1,625.00 |
| Feb 2026 | 8 | $1,499.70 |
| Mar 2026 | 8 | $1,300.00 |
04 Wildcards — smallest per product family
Find the smallest deal in a grouped product family.
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.
> 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.
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:
| Result | Why it happens | Broken → 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:
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.
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,394 | 8 | $6,424.27 | $13,350.00 | $2,249.55 |
| South | $27,648 | 6 | $4,608.02 | $9,250.00 | $1,499.70 |
| East | $31,600 | 5 | $6,319.95 | $10,680.00 | $1,625.00 |
| West | $14,800 | 5 | $2,959.93 | $7,525.00 | $1,300.00 |
| Multi-criteria | |||||
| North + Widget A | $6,250 | 2 | $3,124.80 | $3,749.85 | $2,499.75 |
| North + Gizmo Pro | $24,920 | 2 | $12,460.00 | $13,350.00 | $11,570.00 |
| South + Widget A | $4,999 | 2 | $2,499.50 | $3,124.75 | $1,874.25 |
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:
| Platform | Supports MINIFS? | Notes |
|---|---|---|
| Excel 365 (Windows & Mac) | ✓ Yes | Full support |
| Excel 2021 | ✓ Yes | Full support |
| Excel 2019 | ✓ Yes | Introduced here |
| Excel 2016 / 2013 / 2010 / 2007 | ✗ No | Use MIN(IF()) array formula |
| Excel 2003 and earlier | ✗ No | Use MIN(IF()) array formula |
| Excel for the web | ✓ Yes | Full support |
| Excel on iPad & iPhone | ✓ Yes | Full support |
| Google Sheets | ✓ Yes | Same syntax, same behavior |
| LibreOffice Calc | ✓ Yes | Version 5.2+ |
| Apple Numbers | ✓ Yes | Same 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
-
Check your Excel version
MINIFS needs Excel 2019 or later. Older versions get the NAME error — jump to the MIN(IF()) array fallback.
-
Identify the min_range
What column has the numbers you want the minimum from? That's your FIRST argument.
-
Write your first criteria pair
criteria_range+criteria. Simple text and numbers work as-is; comparisons go in quotes. -
Add more criteria as needed
Each additional filter is another range/criteria pair. All must match — AND logic. Up to 127 pairs.
-
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
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 →