Excel MIN IF Multiple Criteria: How to Find the Smallest Match

Formulas & functions · Updated November 22, 2026

The direct answer: Excel has no single MINIFS function, but you can combine MIN with the IF function as an array formula, or use the MINIFS function (available in Excel 2019 and Microsoft 365) to find the minimum value that meets one or more criteria. For example, =MINIFS(D2:D100, A2:A100, "North", B2:B100, "Widget") returns the smallest sales figure in D2:D100 where column A equals “North” and column B equals “Widget”.

Why MIN with IF? The Array Formula Approach

Before MINIFS existed, the standard way to find a minimum with multiple criteria was an array formula using MIN and IF. The IF function evaluates each condition and returns the value from the result range only when all conditions are true; otherwise it returns FALSE. MIN then ignores the FALSE values and finds the smallest number among the true matches.

For example, with product names in A2:A20, regions in B2:B20, and sales in C2:C20, to find the smallest sale for the “Widget” product in the “East” region, you would enter:

=MIN(IF(A2:A20="Widget", IF(B2:B20="East", C2:C20)))

In older Excel versions (2016 and earlier), you must press Ctrl+Shift+Enter to enter this as an array formula. Excel then wraps it in curly braces like {=MIN(IF(...))}. In Excel 365 and Excel 2021, you can enter it normally because dynamic arrays handle the intermediate array automatically.

This approach works for any number of criteria by nesting IF functions, but it becomes unwieldy with many conditions. It also evaluates the entire range, which can slow down large datasets.

MINIFS: The Modern, Simpler Function

MINIFS was introduced in Excel 2019 and is available in Microsoft 365, Excel 2021, and Excel 2024. It is the direct counterpart to SUMIFS and AVERAGEIFS. The syntax is:

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

You can add up to 126 pairs of criteria ranges and criteria. The function only considers cells in the min_range where all specified conditions are met. It is not an array formula, so you just press Enter.

Example: With the same layout as above, the formula is:

=MINIFS(C2:C20, A2:A20, "Widget", B2:B20, "East")

This returns the smallest sales value for Widgets in the East region. If no rows match, MINIFS returns 0, which can be misleading. To avoid that, you can wrap it in IFERROR or check with COUNTIFS first.

How to Use Wildcards and Cell References in Criteria

Criteria can be numbers, text, expressions, or cell references. For text, you can use wildcards: the asterisk (*) matches any sequence of characters, and the question mark (?) matches any single character. For example, to find the minimum sales for any product starting with “Wid”, use:

=MINIFS(C2:C20, A2:A20, "Wid*")

To use a cell reference for the criteria, simply reference the cell. For instance, if E1 contains “East”, the formula becomes:

=MINIFS(C2:C20, B2:B20, E1)

For numeric comparisons, use operators inside quotes: ">"&E2 or ">=100". For example, to find the minimum sales above 100:

=MINIFS(C2:C20, C2:C20, ">100")

Note that the min_range and the criteria range can be the same column, as shown.

MINIFS vs. MIN with IF: Which Should You Use?

Use MINIFS whenever your Excel version supports it (2019 or later). It is clearer, faster, and less error-prone. Use the MIN+IF array formula only if you are on an older version or if you need to perform a calculation inside the criteria that MINIFS cannot handle directly, such as using a function like LEFT or TODAY on the criteria range.

For example, to find the minimum sales for products whose name starts with “Wid” in older Excel, you would need an array formula like:

=MIN(IF(LEFT(A2:A20,3)="Wid", C2:C20))

MINIFS cannot use LEFT directly on a range, so in that case the array formula is still necessary even in newer versions.

Common Errors and How to Fix Them

Several issues commonly arise when using these formulas:

  • Getting 0 instead of the expected minimum – This usually happens when no rows meet the criteria, or when the min_range contains text or blanks that are being counted. Check your criteria for typos and ensure the ranges are aligned. If you expect no match, use =IF(COUNTIFS(...)>0, MINIFS(...), "No match").
  • Array formula not working – If you forget to press Ctrl+Shift+Enter in older Excel, the formula may return a #VALUE! error or an incorrect result. In Excel 365, you don’t need to.
  • #VALUE! error in MINIFS – This occurs if the criteria ranges are not the same size as the min_range. Make sure all ranges have the same number of rows and columns.
  • Criteria with dates – Dates must be entered as date serial numbers or using the DATE function. For example, to find the minimum sale after January 1, 2024, use ">"&DATE(2024,1,1).
  • Criteria with blanks – To match blank cells, use "=" for empty or "<>" for non-empty. For example, =MINIFS(C2:C20, B2:B20, "") finds the minimum where B is blank.

Alternatives: PivotTables and FILTER

If you need a more dynamic solution, consider a PivotTable: drag the criteria fields to the Filters area and the value field to the Values area, then set the value field to Min. This requires no formulas and updates automatically when you refresh.

In Excel 365, you can also use the FILTER function to extract matching rows and then apply MIN:

=MIN(FILTER(C2:C20, (A2:A20="Widget")*(B2:B20="East")))

This returns the minimum of the filtered array. It is useful if you also want to see the matching rows elsewhere. FILTER requires Excel 365 or Excel 2021.

Performance and Large Datasets

Both MINIFS and array formulas scan the entire range. On very large datasets (hundreds of thousands of rows), MINIFS is generally faster because it is optimized. To improve performance, avoid using entire columns (e.g., A:A) unless necessary; instead, limit the range to the actual data area. Also, using multiple criteria in MINIFS is more efficient than nesting multiple IF functions.

Frequently Asked Questions

Can I use MINIFS with OR logic?

MINIFS only supports AND logic—all criteria must be true. For OR logic, you can use an array formula with MIN and IF, or use MIN with two MINIFS results and take the smaller: =MIN(MINIFS(C2:C20, A2:A20, "Widget"), MINIFS(C2:C20, A2:A20, "Gadget")). In Excel 365, you can also use FILTER with the + operator for OR.

Why does my MINIFS return 0 when there is no match?

MINIFS returns 0 if no cells meet the criteria, which is often misinterpreted as a real minimum. To avoid that, wrap it with IF and COUNTIFS: =IF(COUNTIFS(A2:A20,"Widget",B2:B20,"East")>0, MINIFS(...), "No match").

Does MINIFS work with text criteria?

Yes, text criteria work exactly as in SUMIFS. You can use wildcards, cell references, and comparison operators. For case-insensitive matching, Excel treats text as case-insensitive by default.

What is the difference between MINIFS and MIN with IF in Excel 365?

In Excel 365, both work without CSE. MINIFS is simpler and faster, but MIN with IF allows you to use functions like LEFT or TODAY inside the criteria, which MINIFS cannot. For most uses, MINIFS is preferred.

Can I use MINIFS across multiple sheets?

Yes, you can reference ranges on different sheets, but each criteria range must be the same size. For example, =MINIFS(Sheet2!C2:C20, Sheet2!A2:A20, "Widget") works. You cannot use 3D references across sheets with MINIFS; you would need to combine them manually.

How do I find the minimum value that is greater than zero?

Use a criteria like ">0" on the min_range itself: =MINIFS(C2:C20, C2:C20, ">0"). This ignores zeros and negative numbers.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.