Excel 2019 or laterExamples need Excel 2024 or laterStatistical

MINIFS

MIN.ALS.VOORWAARDEN in Dutch Excel

Returns the smallest number among the rows that meet one or more conditions, and 0 when no row qualifies.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDEF
1Invoice no.StatusDue dateStatusOpen
2F-25-0301Paid15 Sep 2025Oldest due date5 Oct 20255 Oct 2025=MINIFS(C2:C7, B2:B7, F1)=MINIFS(C2:C7; B2:B7; F1)
3F-25-0318Open30 Oct 2025
4F-25-0322Paid12 Oct 2025
5F-25-0340Open21 Nov 2025
6F-25-0355Open5 Oct 2025
7F-25-0361Paid1 Dec 2025

Result: 5 Oct 2025

=MINIFS(C2:C7, B2:B7, F1)
=MINIFS(C2:C7; B2:B7; F1)

What MINIFS does and when to use it

What it does: it returns the smallest value among the rows that meet your criteria, for example the earliest due date of the open invoices.

When to use it: when you need the low end per group, such as the lowest price paid per item or the first invoice date of a supplier. It replaces the array formula MIN(IF(...)) and needs no Ctrl+Shift+Enter.

Two main gains:

  • No array formula: MINIFS is confirmed with Enter, where MIN(IF(...)) needs Ctrl+Shift+Enter in older versions.
  • Several conditions in one formula: you add pairs of range and condition instead of nesting IF inside MIN.

How Excel evaluates it

Excel reads min_range and then the first pair. Row by row it tests the cell in criteria_range1 against criteria1, and a row that passes stays in. With more pairs, a row must pass every test.

Excel then takes the smallest number among the min_range cells of the rows that stayed in. Blank cells and text are not numbers and are skipped.

Watch out: If no row stayed in, Excel returns 0 and not an error, so a result of 0 can mean that nothing matched.

Mental model

Filter the table with your conditions and glance at the smallest number left in the column. If the filter leaves nothing, the glance gives 0, which is lower than any real price.

Terms used on this page: criteria, array, range, cell

Syntax

Official MINIFS documentation
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MINIFS(min_range; criteria_range1; criteria1; [criteria_range2; criteria2]; ...)
ArgumentTypeMeaning
min_rangeRequiredThe cells the smallest value is taken from.
criteria_range1RequiredThe range the first condition is tested against.
criteria1RequiredThe condition: a value, a cell reference or text such as ">=100".
criteria_range2OptionalAn optional second range to test; same size as the first.
criteria2OptionalThe condition that goes with the second range.

Return value:A number: the smallest value among matching rows, or 0 if none match

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Oldest open invoice by due date

While testing the valuation of trade receivables at Veldhuis Installatie BV you have a short list of sales invoices with their status. You want the earliest due date among the invoices that are still open, to see how far back the oldest outstanding item goes. Paid invoices must not count, even when their due date is earlier.

ABCDEFG
1Invoice no.StatusDue dateStatusOpen
2F-25-0301Paid15 Sep 2025Oldest due date5 Oct 20255 Oct 2025=MINIFS(C2:C7, B2:B7, F1)=MINIFS(C2:C7; B2:B7; F1)
3F-25-0318Open30 Oct 2025
4F-25-0322Paid12 Oct 2025
5F-25-0340Open21 Nov 2025
6F-25-0355Open5 Oct 2025
7F-25-0361Paid1 Dec 2025
8
Formula cell (F2)Input

Formula in F2

=MINIFS(C2:C7, B2:B7, F1)
=MINIFS(C2:C7; B2:B7; F1)

Result: 5 Oct 20255 Oct 2025

Step by step
  1. Excel reads F1 and finds Open. That is criteria1, and it is tested against B2:B7.

  2. Excel tests B2:B7 row by row:

    • B2 is Paid: no
    • B3 is Open: yes
    • B4 is Paid: no
    • B5 is Open: yes
    • B6 is Open: yes
    • B7 is Paid: no

    Rows 3, 5 and 6 stay in.

  3. From C2:C7 Excel looks at the cells of those rows only: C3 is 30 October 2025 (serial 45960), C5 is 21 November 2025 (45982) and C6 is 5 October 2025 (45935).
  4. The smallest serial number is 45935, so F2 shows 5 October 2025. The paid invoice F-25-0301 is due on 15 September, which is earlier, but its status is Paid, so it plays no part.
  5. Change F1 to Paid and the result becomes 15 September 2025, the earliest due date among the three paid invoices.

Watch out: Type Opne instead of Open in F1 and the result is 0, which a date format shows as 0 January 1900. Nothing marks it as an error, so a 0 or a year 1900 in this cell means that no row matched.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

The condition selects the open rows first, and the minimum is taken only over their dates. Because a date is a serial number, the smallest number is the oldest date. If F2 shows a number such as 45935, give the cell a date format.

More to watch out for

  • In Excel 2016 or earlier the cell shows #NAME?, because the function needs Excel 2019 or later.

Variations

=MIN(C2:C7)
=MIN(C2:C7)

Without the status condition you get 15 September 2025, which is a paid invoice. Use it only when status does not matter.

Intermediate

Lowest unit price since the new price agreement

The supplier Techniek Groothandel Pols BV renegotiated its prices on 1 July 2025. For your price test you want the lowest unit price invoiced per item since that date, and you want to ignore older invoices at an earlier price.

One recent invoice has no price yet because a credit note is pending, so its price cell is blank. The formula sits in F4 and is meant to be copied down for other items.

ABCDEFG
1ItemInvoice dateUnit pricePrices from1 Jul 2025
2CU-1512 Mar 2025€4.10€ 4,10
3CU-2220 May 2025€6.85€ 6,85ItemLowest price
4CU-158 Jul 2025€4.85€ 4,85CU-15€4.60€ 4,60=MINIFS($C$2:$C$9, $A$2:$A$9, E4, $B$2:$B$9, ">="&$F$1)=MINIFS($C$2:$C$9; $A$2:$A$9; E4; $B$2:$B$9; ">="&$F$1)
5VLV-0814 Aug 2025€12.95€ 12,95
6CU-1522 Sep 2025€4.60€ 4,60
7CU-223 Oct 2025€7.45€ 7,45
8CU-1530 Oct 2025
9CU-1517 Nov 2025€4.75€ 4,75
10
Formula cell (F4)InputNote: C8 is deliberately empty: the invoice has no price yet.

Formula in F4

=MINIFS($C$2:$C$9, $A$2:$A$9, E4, $B$2:$B$9, ">="&$F$1)
=MINIFS($C$2:$C$9; $A$2:$A$9; E4; $B$2:$B$9; ">="&$F$1)

Result: €4.60€ 4,60

Step by step
  1. Excel reads the first pair: criteria_range1 is A2:A9 and criteria1 is E4, CU-15. Rows 2, 4, 6, 8 and 9 have that item.
  2. Excel reads the second pair. F1 holds 1 July 2025, serial 45839. The & turns it into the text ">=45839", so a row passes when its date in B is 45839 or higher. Row 2 (12 March 2025) fails; rows 4, 6, 8 and 9 pass.
  3. The prices of rows 4, 6, 8 and 9 are 4.85, 4.60, blank and 4.75. The blank cell is not a number, so Excel skips it. It is not treated as 0.
  4. The smallest of 4.85, 4.60 and 4.75 is 4.60, so F4 shows 4.60. Without the date pair the answer would be 4.10, the price from March that no longer applies.
  5. Copy the formula to F5 and type CU-22 in E5: the ranges stay fixed, E5 is the item, and the result is 7.45, because the 6.85 invoice dates from before 1 July.

Watch out: A price that was typed as 0 instead of left blank is a number and wins every time: the result would be 0.00. Look for zeros in the price column before trusting the minimum.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

The second pair works on the date column while the minimum is taken from the price column, so each range plays its own part. The $ signs keep the three ranges fixed and E4 is relative, which is what makes the formula copyable. Blank cells in min_range are skipped, which is why a missing price does not turn into a lowest price of 0.

More to watch out for

  • Write ">=F1" without the & and Excel compares with the text F1. No date passes, and the result is 0.

Variations

=MINIFS($C$2:$C$9, $A$2:$A$9, E4, $B$2:$B$9, ">="&DATE(2025, 7, 1))
=MINIFS($C$2:$C$9; $A$2:$A$9; E4; $B$2:$B$9; ">="&DATE(2025; 7; 1))

The date typed into the formula. It works, but a reviewer has to open the formula to see the date. A cell like F1 is quicker to check.

Advanced

Flag lines priced over 10% above the item's lowest price

In your purchase price analysis you have the invoice lines of three suppliers for three items. You want a flag next to each line: Review when the unit price is more than the tolerance in F1 (10%) above the lowest price paid for the same item, and OK otherwise. The formula in D2 is copied down, so every line is compared with the lowest price of its own item.

ABCDEFG
1ItemSupplierUnit priceFlagTolerance0.10,1
2KB-12Wolters Fittingen BV€8.40€ 8,40Review=IF(C2 > MINIFS($C$2:$C$9, $A$2:$A$9, A2) * (1 + $F$1), "Review", "OK")=IF(C2 > MINIFS($C$2:$C$9; $A$2:$A$9; A2) * (1 + $F$1); "Review"; "OK")
3SL-30Aalbers Montage BV€15.30€ 15,30
4KB-12Veenstra Staalhandel BV€7.60€ 7,60
5BM-05Wolters Fittingen BV€3.25€ 3,25
6SL-30Veenstra Staalhandel BV€14.20€ 14,20
7KB-12Aalbers Montage BV€8.10€ 8,10
8BM-05Aalbers Montage BV€3.05€ 3,05
9SL-30Wolters Fittingen BV€14.85€ 14,85
10
Formula cell (D2)Input

Formula in D2

=IF(C2 > MINIFS($C$2:$C$9, $A$2:$A$9, A2) * (1 + $F$1), "Review", "OK")
=IF(C2 > MINIFS($C$2:$C$9; $A$2:$A$9; A2) * (1 + $F$1); "Review"; "OK")

Result: Review

Step by step
  1. Excel evaluates the test of IF first, and inside it MINIFS first. A2 holds KB-12, so criteria1 is KB-12 and the range is $A$2:$A$9.
  2. Rows 2, 4 and 7 hold KB-12, with prices 8.40, 7.60 and 8.10. MINIFS returns the smallest: 7.60. The line itself is one of the rows, which is harmless because its own price can never be lower than the minimum.
  3. Excel then calculates the limit: 7.60 × (1 + 0.10) = 8.36.
  4. The comparison is 8.40 > 8.36, which is TRUE, so IF returns its second argument: Review.
  5. Copied to D3, A2 becomes A3 (SL-30) and C2 becomes C3 (15.30). MINIFS finds 15.30, 14.20 and 14.85, so the minimum is 14.20 and the limit is 15.62. The test 15.30 > 15.62 is FALSE, so D3 shows OK.

Watch out: Prices stored as text (for example from a PDF export) are skipped by MINIFS. The lowest price is then too high, and fewer lines are flagged than should be. The formula shows no error. Check the column with COUNT against the number of lines.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

A2 is relative, so every copy asks for the lowest price of its own item, while the three ranges are locked. The tolerance sits in a cell next to the data, so the limit can change without anyone editing the formula. Because MINIFS is evaluated for each row, the comparison base is always the lowest price among all lines for that item.

More to watch out for

  • A trailing space in some item codes does not give 0. A line with "KB-12 " still finds itself, but the lines with and without the space form two separate groups, and each line is compared with the lowest price of its own group only. A line far above the true lowest price can then pass as OK.

    Clean the code column with TRIM, or compare COUNTIF($A$2:$A$9, A2) with the number of lines you expect for the item.

  • The lowest price is not necessarily the right price. A mistyped 0.76 in the data makes every other line of that item a Review.

Variations

=C2 / MINIFS($C$2:$C$9, $A$2:$A$9, A2) - 1
=C2 / MINIFS($C$2:$C$9; $A$2:$A$9; A2) - 1

Shows how far each line is above the lowest price as a percentage, which you can sort on. Use it when the size of the gap matters, not only whether it passes the tolerance.

MasterExcel 2024 or later

Suppliers whose first invoice falls in the new period

For the new supplier test you want a list of the suppliers that were first invoiced on or after 1 October 2025, together with the date of that first invoice, so you can check how each one was approved. The invoice list holds every supplier several times. The result must be one line per supplier, and the sheet must say so when there are no new suppliers.

ABCDEFG
1SupplierInvoice dateAmountNew since1 Oct 2025
2Techniek Groothandel Pols BV14 Feb 2025€5,200.00€ 5.200,00
3Van Rijn Leidingwerk BV9 Oct 2025€3,400.00€ 3.400,00Van Rijn Leidingwerk BV=LET( suppliers, UNIQUE(A2:A9), first, MINIFS(B2:B9, A2:A9, suppliers), isNew, first >= F1, FILTER(HSTACK(suppliers, first), isNew, "No new suppliers") )=LET( suppliers; UNIQUE(A2:A9); first; MINIFS(B2:B9; A2:A9; suppliers); isNew; first >= F1; FILTER(HSTACK(suppliers; first); isNew; "No new suppliers") )9 Oct 2025
4Techniek Groothandel Pols BV21 Oct 2025€1,800.00€ 1.800,00Tromp Dakmaterialen BV4 Dec 2025
5Hofstra Installatiemateriaal BV3 Jun 2025€2,250.00€ 2.250,00
6Van Rijn Leidingwerk BV26 Nov 2025€2,900.00€ 2.900,00
7Tromp Dakmaterialen BV4 Dec 2025€7,650.00€ 7.650,00
8Hofstra Installatiemateriaal BV11 Dec 2025€1,300.00€ 1.300,00
9Tromp Dakmaterialen BV18 Dec 2025€980.00€ 980,00
10
Formula cell (E3)InputSpilled resultNote: The result spills into E3:F4. In a real sheet the invoice list would run to row 20,000 and cover the whole supplier history.

Formula in E3

=LET(
  suppliers, UNIQUE(A2:A9),
  first, MINIFS(B2:B9, A2:A9, suppliers),
  isNew, first >= F1,
  FILTER(HSTACK(suppliers, first), isNew, "No new suppliers")
)
=LET(
  suppliers; UNIQUE(A2:A9);
  first; MINIFS(B2:B9; A2:A9; suppliers);
  isNew; first >= F1;
  FILTER(HSTACK(suppliers; first); isNew; "No new suppliers")
)

Result: Van Rijn Leidingwerk BV, 9 Oct 2025; Tromp Dakmaterialen BV, 4 Dec 2025Van Rijn Leidingwerk BV. 9 Oct 2025; Tromp Dakmaterialen BV. 4 Dec 2025

Step by step
  1. LET starts with suppliers. UNIQUE(A2:A9) removes the duplicates and keeps the order of first appearance: Techniek Groothandel Pols BV, Van Rijn Leidingwerk BV, Hofstra Installatiemateriaal BV and Tromp Dakmaterialen BV.

  2. Next is first. MINIFS gets a range as its criterion, so it runs once per supplier and returns the earliest invoice date of each:

    • Pols: 14 February 2025 (45702)
    • Van Rijn: 9 October 2025 (45939)
    • Hofstra: 3 June 2025 (45811)
    • Tromp: 4 December 2025 (45995)
  3. isNew compares each first date with F1, 1 October 2025 (45931): 45702 is lower, 45939 is higher, 45811 is lower and 45995 is higher. The result is FALSE, TRUE, FALSE, TRUE.
  4. HSTACK places the names and the first dates side by side as a block of 4 rows and 2 columns. FILTER keeps the rows where isNew is TRUE: the second and the fourth.
  5. The last argument of LET is the calculation, so its result is what E3 shows: Van Rijn Leidingwerk BV with 45939 and Tromp Dakmaterialen BV with 45995, spilled over E3:F4. Column F carries a date format, so it shows 9 October 2025 and 4 December 2025.
  6. Set F1 to 1 January 2026 and no first date qualifies, so FILTER returns its third argument, No new suppliers.

Watch out: The mirror image looks right: with MAXIFS instead of MINIFS you get each supplier's latest invoice, and every supplier with an invoice after 1 October qualifies. Pols (21 October) and Hofstra (11 December) then appear as new, although both were used before.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

MINIFS reduces the repeated invoice lines to one date per supplier, and the comparison with F1 turns that into a TRUE or FALSE per supplier that FILTER can use.

The result is only as good as the history in the list: if the list starts on 1 October 2025, every supplier looks new. To prove that a supplier is new, compare with last year's supplier list instead, for example with XMATCH or COUNTIF, which does not depend on how far back the invoice list reaches.

For hundreds of thousands of lines a PivotTable with the minimum of the invoice date per supplier is faster, at the price of a manual refresh.

More to watch out for

  • A supplier whose invoice dates are all blank gets 0 from MINIFS. Since 0 is lower than F1, the supplier is dropped silently instead of being reported.
  • UNIQUE and FILTER need Excel 2021 or Microsoft 365; HSTACK needs Excel 2024 or Microsoft 365. In Excel 2019 the formula shows #NAME?; MINIFS itself works there.

Variations

=MINIFS($B$2:$B$9, $A$2:$A$9, A2)
=MINIFS($B$2:$B$9; $A$2:$A$9; A2)

The first invoice date of the supplier on each line, copied down. Without UNIQUE and FILTER this works in Excel 2019, at the price of one line per invoice.

Try it yourself

Type a criterion for the status and see the smallest invoice among those that pass.

ABCDEF
1Invoice no.StatusAmount
2F-25-0301Paid€4,200.00€ 4.200,00Smallest€1,850.00€ 1.850,00=MINIFS(C2:C8, B2:B8, "x")=MINIFS(C2:C8; B2:B8; "x")
3F-25-0318Open€1,850.00€ 1.850,00
4F-25-0322Paid€960.00€ 960,00
5F-25-0340Open€7,300.00€ 7.300,00
6F-25-0355Open€2,650.00€ 2.650,00
7F-25-0361Paid€3,125.00€ 3.125,00
8F-25-0372€5,200.00€ 5.200,00
  • Formula cell
  • Meets the criterion
=MINIFS(C2:C8, B2:B8, "Open")
=MINIFS(C2:C8; B2:B8; "Open")

Result: €1,850.00€ 1.850,00

Common mistakes

SymptomCauseFix
#NAME? in Excel 2016 or earlierThe workbook is opened in Excel 2016 or earlier and the cell shows #NAME?, because MINIFS does not exist there.The function arrived in Excel 2019. For older versions use =MIN(IF(...)) confirmed with Ctrl+Shift+Enter, or AGGREGATE(15, 6, range/(condition), 1), where option 6 skips the errors that the division creates for rows that do not qualify.
0 shown although no row matchesA 0 is read as the lowest value, while no row met the conditions (an item code with a typo or a trailing space). In a price check that 0 looks like the best price ever.Count the matching rows with COUNTIFS and the same conditions, or wrap the formula: IF(COUNTIFS(...) = 0, "No match", MINIFS(...)).
Price check shows 0 as the best priceA 0 typed as a placeholder for 'price not known yet' sits in min_range. It is a number, so it is always the minimum.Leave unknown values blank, or add a condition such as ">0" on the same range.
#VALUE! with ranges of different sizeThe ranges differ in size, for example C2:C9 for min_range and A2:A8 for criteria_range1, and the result is #VALUE!.Select the same rows in every range, and lock them with $ when you copy the formula down.