Skip to content

Excel function · Statistics

Excel MINIFS function

MINIFS returns the smallest value among the rows that meet your conditions: the lowest quote for each product, a customer's first order date, the lowest stock level that is not zero.

In Turkish Excel: ÇOKEĞERMİN

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

English Excel=MINIFS(min_range, criteria_range1, criteria1, ...)
Turkish Excel=Ç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

ArgumentRequiredDescription
min_rangeRequiredThe cells to search for the smallest value, such as a price or date column.
criteria_range1RequiredThe range tested by the first condition; the same size as min_range.
criteria1RequiredThe 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.

F2fx=MINIFS($C$2:$C$7,$A$2:$A$7,E2)
The lowest quote for each product: example table
ABCDEF
1ProductSupplierPriceProductLowest
2Grounded socketBrown & Sons94.50Grounded socket89.90
3Grounded socketNorthgate Ltd89.90Circuit breaker127.75
4Circuit breakerBrown & Sons132.00
5Grounded socketHarbour Trade92.00
6Circuit breakerStar Market127.75
7Circuit breakerNorthgate Ltd129.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.

F2fx=MINIFS($A$2:$A$7,$B$2:$B$7,E2)
Each customer's first order date: example table
ABCDEF
1DateCustomerAmountCustomerFirst Order
212-Sep-2025Brown & Sons4200Brown & Sons12-Sep-2025
32-Oct-2025Northgate Ltd12500Northgate Ltd2-Oct-2025
49-Oct-2025Brown & Sons8300Harbour Trade27-Aug-2025
527-Aug-2025Harbour Trade9900
621-Oct-2025Northgate Ltd3100
730-Sep-2025Harbour Trade15200
  • 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.

F2fx=MINIFS($C$2:$C$7,$A$2:$A$7,E2,$C$2:$C$7,">0")
Lowest stock that is not zero, per warehouse: example table
ABCDEF
1WarehouseProductStockWarehouseLowest Stock
2LeedsGrounded socket250Leeds48
3LeedsCircuit breaker0York64
4YorkGrounded socket64
5LeedsLight switch48
6YorkLight switch0
7YorkCable duct120
  • 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

Type this in Nexell

Make a summary table with the lowest quoted price for each product from the quote list

Convert to table

Bring 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.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free