MINIFS in Excel: Find the Smallest Value That Matches
By ExcelReady TeamUpdated 7 min read
The short answer
MINIFS returns the smallest number in a range among the rows that meet one or more conditions. Write =MINIFS(min_range, criteria_range1, criteria1, ...), for example =MINIFS(D2:D9,A2:A9,"Paper") for the cheapest paper quote. Every condition must be true. It returns 0 when no row matches, and needs Excel 2019 or later, or Microsoft 365.
Key takeaways
- MINIFS puts the range of numbers first, then pairs of criteria range and criteria.
- Every condition must be true for a row to count (AND logic).
- MINIFS returns 0 when nothing matches, so check the count before you trust a 0.
- Add the condition ">0" on the number column to skip zero placeholders.
- In Excel 2016 and older, use MIN with IF, or AGGREGATE, instead.
On this page+
MINIFS in Excel finds the smallest number among the rows that match your conditions: the cheapest quote for one item, the earliest date for one project, the lowest score in one team. It works like MIN with a filter built in. This guide shows the syntax, examples with one and two conditions, how to skip zeros, and what to do when MINIFS returns 0 or isn't available in your version.
What does MINIFS do in Excel?#
MINIFS checks each row against one or more conditions and returns the smallest number from the rows that pass. The syntax is:
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Part | What it means | Example |
|---|---|---|
| min_range | The numbers to find the smallest of | D2:D9 |
| criteria_range1 | The first column to check | A2:A9 |
| criteria1 | What that column must match | "Paper", ">0", G2 |
| more pairs | Optional extra conditions | C2:C9,"North" |
Every condition must be true for a row to count. MINIFS needs Excel 2019 or later, or Microsoft 365. Empty cells and text in the min range are ignored, but a real 0 counts as a number. The plain MIN function guide covers the version without conditions.
All the examples use this table of supplier quotes in A1:D9:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Supplier | Region | Price |
| 2 | Paper | Apex | North | 4.20 |
| 3 | Paper | Brill | South | 3.95 |
| 4 | Toner | Apex | North | 61.00 |
| 5 | Paper | Cora | North | 4.05 |
| 6 | Toner | Brill | South | 58.50 |
| 7 | Toner | Cora | South | 0.00 |
| 8 | Paper | Apex | South | 4.10 |
| 9 | Toner | Apex | South | 59.75 |
The 0 in row 7 is a placeholder: Cora didn't send a toner quote, and someone typed 0 instead of leaving the cell empty.
How to use MINIFS step by step#
To find the cheapest paper quote, give MINIFS the prices first, then the column to check and the item name:
- Click an empty cell, for example G2.
- Type
=MINIFS(and select the prices, D2:D9. - Type a comma, select the Item column, A2:A9, and type
,"Paper". - Close the bracket and press Enter.
=MINIFS(D2:D9,A2:A9,"Paper")The result is 3.95, Brill's quote in row 3. Text criteria need double quotes, and MINIFS isn't case-sensitive, so "paper" works too.
To use a cell as the condition, point at it without quotes. If F2 holds Paper, =MINIFS($D$2:$D$9,$A$2:$A$9,F2) gives the same 3.95. Lock the ranges with F4 (Windows) or ⌘T (Mac), then fill down with Ctrl+D (Windows) or ⌘D (Mac) to get the minimum for each item in a list.
MINIFS with two or more conditions#
Add another pair of criteria range and criteria for each extra condition. The cheapest paper quote in the North region:
=MINIFS(D2:D9,A2:A9,"Paper",C2:C9,"North")The result is 4.05 (Cora, row 5). Brill's 3.95 is cheaper but is in the South, so it fails the second condition.
Conditions can also compare numbers. Operators go inside the quotes: ">=4" means 4 or more. Text conditions accept wildcards, where * stands for any characters. =MINIFS(D2:D9,B2:B9,"A*") finds the lowest quote from any supplier whose name starts with A: 4.10 (Apex paper in the South).
How to skip zeros with MINIFS#
If zeros in your data mean "missing", add the number column as its own condition with ">0". Without it, the cheapest toner looks free:
=MINIFS(D2:D9,A2:A9,"Toner")This returns 0, from Cora's placeholder. Add the extra pair:
=MINIFS(D2:D9,A2:A9,"Toner",D2:D9,">0")Now the result is 58.50, Brill's real quote. The better long-term fix is to leave missing values empty, because MINIFS skips empty cells on its own.
Why does MINIFS return 0?#
MINIFS returns 0 when no row meets every condition, which looks just like a real minimum of 0. For example, =MINIFS(D2:D9,A2:A9,"Ink") returns 0 because there are no ink quotes. Check these causes:
- Spelling and spaces. "Paper " with a trailing space doesn't match "Paper". Clean the column with TRIM.
- Numbers stored as text. Prices imported as text are ignored. Convert them with VALUE or Data > Text to Columns.
- A cell reference inside quotes.
">=G1"compares with the letters G1. Write">="&G1. - Ranges of different sizes. If the min range and a criteria range don't match in size, MINIFS returns #VALUE!, not 0.
To show a message instead of a misleading 0, count first:
=IF(COUNTIF(A2:A9,"Ink")=0,"No quotes",MINIFS(D2:D9,A2:A9,"Ink"))This returns No quotes. The same 0 behaviour applies to MAXIFS; the guide to MAXIFS and MINIFS together shows both side by side.
How to find who has the lowest value#
MINIFS returns the number, not the name next to it. To get the supplier with the cheapest paper quote, find the row where both the item and the price match:
=XLOOKUP(1,(A2:A9="Paper")*(D2:D9=MINIFS(D2:D9,A2:A9,"Paper")),B2:B9)The result is Brill. The two tests in brackets each give TRUE or FALSE for every row, and multiplying them gives 1 only where both are true. XLOOKUP then finds that 1. If two suppliers tie on price, this returns the first one. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365.
MINIFS in Excel 2016 and older#
In versions before Excel 2019, MINIFS shows #NAME?. Two older formulas do the same job:
=MIN(IF(A2:A9="Paper",D2:D9))
=AGGREGATE(15,6,D2:D9/(A2:A9="Paper"),1)Both return 3.95. The first is an array formula: in Excel 2016 and older, confirm it with Ctrl+Shift+Enter on Windows or ⌘+Shift+Return on a Mac. The second uses AGGREGATE's SMALL function (15) with option 6, which ignores errors. Rows that fail the test divide by zero, so AGGREGATE skips them. It needs no special keys and works in Excel 2010 and later.
Need the average or the highest instead? Learn how AVERAGEIF finds a conditional average, see AVERAGEIFS with several conditions, and compare the two in AVERAGEIF vs AVERAGEIFS.
Practise it#
Try the exercise on this page first: it needs two conditions, so a plain MIN can't do it. Then practise AVERAGEIF, MAXIFS and MINIFS in a live spreadsheet, where you type each formula and it is checked instantly.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
What is the cheapest Paper quote in the North region? In H2, use MINIFS with two conditions: Item (A2:A9) is Paper and Region (C2:C9) is North. The prices are in D2:D9.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Price | ||||||||
| 2 | Cheapest North paper | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 |
Tip: Start with the prices, D2:D9. Then add two pairs: A2:A9 with "Paper", and C2:C9 with "North".
Practise this in a lesson
Frequently asked questions
What is the MINIFS formula in Excel?
The formula is =MINIFS(min_range, criteria_range1, criteria1, ...). The min range holds the numbers, and each pair after it is a column to check and the value it must match. It returns the smallest matching number.
Why does MINIFS return 0?
Either no row meets all the conditions, or a 0 in the data really is the smallest match. Check for spelling differences and extra spaces, and add ">0" as a condition if zeros mean missing values.
Can MINIFS ignore zeros?
Yes. Add the number column as a criteria range with ">0", for example =MINIFS(D2:D9,A2:A9,"Toner",D2:D9,">0").
What can I use instead of MINIFS in Excel 2016?
Use =MIN(IF(A2:A9="Paper",D2:D9)) confirmed with Ctrl+Shift+Enter, or =AGGREGATE(15,6,D2:D9/(A2:A9="Paper"),1), which needs no special keys.
Does MINIFS work with dates?
Yes. Dates are numbers in Excel, so MINIFS returns the earliest matching date. Format the result cell as a date or you will see a serial number like 45672.