What it does
It is written like MAXIFS: first the range to search for the smallest value, then range and condition pairs. =MINIFS(C2:C50,A2:A50,"Grounded socket") returns the lowest quote received for that product. MINIFS is available in Excel 2019 and Microsoft 365.
To skip zeros, give the searched range itself as a condition: =MINIFS(B2:B20,B2:B20,">0"). With a date column it finds the first date; give the result a date format.
If no row meets the conditions, the result is 0, which is misleading when you look for a minimum, because 0 looks like the lowest quote. Check with COUNTIFS: =IF(COUNTIFS(A2:A50,E2)=0,"No quote",MINIFS(C2:C50,A2:A50,E2)).
Syntax
=MINIFS(min_range, criteria_range1, criteria1, ...)=ÇOKEĞERMİN(min_aralığı; ölçüt_aralığı1; ölçüt1; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
min_range | Required | The cells to search for the smallest value, such as a price or date column. |
criteria_range1 | Required | The range tested by the first condition; the same size as min_range. |
criteria1 | Required | The first condition: a value, a comparison such as ">0" or a cell. Further conditions follow as range and criteria pairs. |
Examples
1The lowest quote for each product
Several suppliers quoted for the same products. The formula in F2 is filled down and finds the lowest of each product's quotes.
=MINIFS($C$2:$C$7,$A$2:$A$7,E2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Supplier | Price | Product | Lowest | |
| 2 | Grounded socket | Brown & Sons | 94.50 | Grounded socket | 89.90 | |
| 3 | Grounded socket | Northgate Ltd | 89.90 | Circuit breaker | 127.75 | |
| 4 | Circuit breaker | Brown & Sons | 132.00 | |||
| 5 | Grounded socket | Harbour Trade | 92.00 | |||
| 6 | Circuit breaker | Star Market | 127.75 | |||
| 7 | Circuit breaker | Northgate Ltd | 129.40 |
- F2 (filled down to F3)
=MINIFS($C$2:$C$7,$A$2:$A$7,E2)
The lowest quote for the socket is 89.90 and for the breaker 127.75.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Each customer's first order date
When the range searched for the smallest value is the date column, MINIFS returns each customer's earliest, that is first, order date.
=MINIFS($A$2:$A$7,$B$2:$B$7,E2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | First Order | |
| 2 | 12-Sep-2025 | Brown & Sons | 4200 | Brown & Sons | 12-Sep-2025 | |
| 3 | 2-Oct-2025 | Northgate Ltd | 12500 | Northgate Ltd | 2-Oct-2025 | |
| 4 | 9-Oct-2025 | Brown & Sons | 8300 | Harbour Trade | 27-Aug-2025 | |
| 5 | 27-Aug-2025 | Harbour Trade | 9900 | |||
| 6 | 21-Oct-2025 | Northgate Ltd | 3100 | |||
| 7 | 30-Sep-2025 | Harbour Trade | 15200 |
- F2 (filled down to F4)
=MINIFS($A$2:$A$7,$B$2:$B$7,E2)
Brown & Sons placed its first order on 12-Sep-2025 and Harbour Trade on 27-Aug-2025.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Lowest stock that is not zero, per warehouse
In the stock count, sold-out products were entered as 0. The stock column itself is given as a second condition with ">0", so zeros are skipped.
=MINIFS($C$2:$C$7,$A$2:$A$7,E2,$C$2:$C$7,">0")| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Warehouse | Product | Stock | Warehouse | Lowest Stock | |
| 2 | Leeds | Grounded socket | 250 | Leeds | 48 | |
| 3 | Leeds | Circuit breaker | 0 | York | 64 | |
| 4 | York | Grounded socket | 64 | |||
| 5 | Leeds | Light switch | 48 | |||
| 6 | York | Light switch | 0 | |||
| 7 | York | Cable duct | 120 |
- F2 (filled down to F3)
=MINIFS($C$2:$C$7,$A$2:$A$7,E2,$C$2:$C$7,">0")
The lowest stock that is not zero is 48 in Leeds and 64 in York. Without the second condition, both warehouses would show 0.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Result is 0
Cause: No row meets the conditions, and MINIFS returns 0 in that case.
Fix: Check with COUNTIFS first whether any rows match, and show a message if none do.
- #VALUE!
Cause: A criteria range is not the same size as min_range.
Fix: Start and end every range on the same rows.
- #NAME?
Cause: The file was opened in an older version of Excel that does not know MINIFS.
Fix: In older versions, enter
=MIN(IF(A2:A50="Socket",C2:C50))with Ctrl+Shift+Enter.
Good to know
- To find the supplier behind the lowest quote, use FILTER in Microsoft 365:
=FILTER(B2:B50,(A2:A50=E2)*(C2:C50=F2))looks for the product and the lowest price together. - Put a customer's first and last order dates side by side to see how long they have been a customer.
In Nexell
Get Nexell to do it from a sentence
Make a summary table with the lowest quoted price for each product from the quote list
Convert to tableBring your quote list into the tool and type this. Nexell builds the summary with real Excel formulas and shows it in the preview first; in the downloaded file, the summary updates as new quotes come in.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I find the minimum value with a condition in Excel?
Use =MINIFS(C2:C50,A2:A50,"Socket"). Add more range and criteria pairs for more conditions: =MINIFS(C2:C50,A2:A50,"Socket",D2:D50,"In stock").
Why does MINIFS return 0?
Either no row meets the conditions, or one of the matching rows really contains 0. Check the number of rows with =COUNTIFS(...); add a ">0" condition to skip zeros.