Excel MIN IF: Find the Smallest Value That Meets a Test

Formulas & functions · Updated November 18, 2026

To find the smallest value in Excel that meets a condition, use MINIFS in Microsoft 365: with regions in B2:B7 and values in C2:C7, enter =MINIFS(C2:C7,B2:B7,"East"). The result is the lowest value for rows where the region is East. Excel has no function named MINIF; for older versions without MINIFS, use an array formula built with MIN and IF.

What “MIN IF” Means in Excel

People searching for “Excel MIN IF” usually want a conditional minimum: the lowest number among the values whose corresponding rows satisfy a test. For example, a delivery log might contain a region and a number of delivery days for each order. You may want the shortest delivery time for East orders, rather than the shortest time in the entire log.

The ordinary MIN function returns the smallest number in a range, without applying a row-by-row condition. A conditional minimum adds a test that determines which values are eligible. Excel provides MINIFS for this purpose in Microsoft 365 and current perpetual editions such as Excel 2019, 2021, and 2024. There is not a separate built-in function called MINIF.

The term “IF” can also refer to the older formula pattern MIN(IF(...)). That approach evaluates a condition and passes qualifying values to MIN. It remains useful when a workbook must work in a version that does not include MINIFS.

Use MINIFS for a Conditional Minimum

MINIFS takes a range of values to minimize, followed by one or more pairs of a criteria range and a criterion. Its basic structure is MINIFS(min_range, criteria_range1, criteria1, ...). The min range contains the numbers or dates to compare; the criteria range contains the values Excel checks.

Suppose B2:B7 contains the regions East, West, East, South, East, and West, while C2:C7 contains delivery times 8, 4, 5, 3, 7, and 6. With those values in B2:B7 and C2:C7, enter =MINIFS(C2:C7,B2:B7,"East"). It returns 5, the smallest delivery-time value on an East row. The overall minimum is 3, but that row is South, so it does not qualify.

The order of the arguments matters: the first range is the one Excel minimizes. The condition does not go before it. Also make sure the min range and each criteria range cover corresponding rows and have the same dimensions. For example, using C2:C7 with B2:B8 can produce a #VALUE! error.

Apply More Than One Condition

To require several conditions at once, add another criteria-range and criterion pair. MINIFS treats these pairs as AND conditions: a row must satisfy every condition to contribute its value. This is handy for finding the lowest price for a specific product in a specific store, or the earliest date for a team during a selected period.

For example, suppose B2:B7 contains regions, C2:C7 contains delivery days, and D2:D7 contains service types. With the same six order rows, regions in B2:B7, days in C2:C7, and service labels in D2:D7, enter =MINIFS(C2:C7,B2:B7,"East",D2:D7,"Express"). Excel returns the smallest delivery-day value only among rows marked both East and Express. The result depends on the actual labels and numbers in the workbook.

You can also add numeric boundaries as criteria. With regions in B2:B7 and delivery days in C2:C7, enter =MINIFS(C2:C7,B2:B7,"East",C2:C7,">=5") to find the smallest East value that is at least 5. If the qualifying East values are 5, 7, and 8, the result is 5. The comparison operator is supplied as text in the criterion.

Write Criteria for Text, Numbers, and Dates

A criterion can be a fixed value, a comparison, a wildcard pattern, or a reference to a cell. Cell references make a worksheet easier to reuse because someone can change the condition without editing the formula. If E2 contains East, and regions are in B2:B7 while values are in C2:C7, enter =MINIFS(C2:C7,B2:B7,E2).

  • Exact text: Use a text criterion such as "East". Excel does not distinguish uppercase from lowercase in ordinary criteria matching.
  • Comparisons: Criteria such as ">10", "<=25", or "<>0" test numbers. To build a comparison from a cell, join the operator and the cell reference with &.
  • Wildcards: An asterisk matches any sequence of characters, and a question mark matches one character. For example, "North*" can match labels beginning with North. Use a tilde to treat a wildcard character literally.
  • Dates: Excel stores real dates as serial numbers, so criteria can test a date range. Use actual date values or construct criteria from date cells rather than relying on ambiguous text such as 3/4/26, which may be interpreted differently by regional settings.

When a criterion is built from a comparison operator and a cell, the operator must be joined to the reference. With values in C2:C7, regions in B2:B7, and a lower limit in E2, enter =MINIFS(C2:C7,B2:B7,"East",C2:C7,">="&E2). This lets the user change E2 while keeping the formula intact. Excel installations using a regional list separator may display semicolons instead of commas; use the separator required by that installation.

Use MIN and IF in Older Excel Versions

If the workbook needs to run in an Excel version without MINIFS, the traditional alternative is MIN(IF(...)). The IF expression returns each value when its row meets the condition and FALSE otherwise; MIN then finds the smallest qualifying number. In Microsoft 365, with regions in B2:B7 and values in C2:C7, enter =MIN(IF(B2:B7="East",C2:C7)). For the sample data above, it returns 5.

In older Excel releases that do not support dynamic array calculations, confirm this as an array formula: select the formula cell, type the formula, and press Ctrl+Shift+Enter rather than Enter. Excel may show curly braces around the formula in the formula bar; do not type those braces yourself. Microsoft 365 accepts the formula with Enter. On a Mac, the relevant array-entry shortcut in older Excel versions is Control+Shift+Return; current versions can generally enter the formula normally.

For two conditions, the IF test can combine them with multiplication, which acts like AND for TRUE/FALSE tests. With regions in B2:B7, service labels in D2:D7, and values in C2:C7, enter =MIN(IF((B2:B7="East")*(D2:D7="Express"),C2:C7)). In older versions this also needs array entry. For current Excel, MINIFS is usually clearer and easier to maintain.

Handle No Matches and Zero Values

A common surprise is that MINIFS returns 0 when no rows meet its conditions. It does not have an optional “if no match” argument. That can be confusing if zero is also a legitimate minimum in the data, so check whether any row matched before interpreting a zero result.

With regions in B2:B7 and the requested region in E2, use COUNTIFS to count qualifying rows. Enter =COUNTIFS(B2:B7,E2). If E2 contains a region that appears three times in B2:B7, the result is 3; if it does not appear, the result is 0. You can use this count alongside the minimum formula or build a message around it with an IF formula.

For a single-condition check, with regions in B2:B7, values in C2:C7, and the requested region in E2, enter =IF(COUNTIFS(B2:B7,E2)=0,"No match",MINIFS(C2:C7,B2:B7,E2)). It returns the minimum for a matching region and the text No match otherwise. If zero is a valid value, this separate match test prevents a real zero from being mistaken for the no-match result.

Common Problems to Check

  • Wrong range order: Confirm the first argument is the range containing values to minimize, not the range containing the condition.
  • Unequal range sizes: The min range and criteria ranges must align row for row and have matching dimensions.
  • Numbers stored as text: A value that looks like a number may be text after an import. Convert it to a numeric value if it should take part in the minimum.
  • Unexpected blanks: Blank cells in the min range are not meaningful numeric candidates for a minimum. Check for missing data if the result seems inconsistent.
  • Dates stored as text: Text that resembles a date may not behave like a true Excel date. Convert the column to real date values before using date boundaries.
  • Criteria do not match: Look for extra spaces, inconsistent labels, or a criterion that refers to the wrong cell. A cell containing East followed by a space is not necessarily the same label as East.

Errors in source data can also affect the result, so inspect the cells in both the values and criteria ranges if a formula reports an error. A quick way to troubleshoot is to test the criteria by filtering the table or counting matches, then verify that the qualifying values are numeric and that the selected ranges refer to the intended rows.

MIN, MINIFS, and MIN(IF): Which Should You Choose?

Use MIN when every value in a range is eligible. Use MINIFS when one or more conditions determine which values count and the workbook targets a version that supports it. Choose MIN(IF(...)) when compatibility with older Excel is important or when the condition logic is more flexible than a straightforward set of criteria pairs.

MINIFS is normally the most readable choice for multiple AND conditions. It is also a regular formula in current Excel, so you do not need the special array-entry step required by older versions of MIN(IF(...)). However, conditions combined as OR are not expressed as a single ordinary criteria-pair set in MINIFS; consider separate calculations or an array formula designed for the exact logic. Be careful combining separate minima if one condition has no matches, because its zero result may incorrectly become the overall minimum.

When sharing a workbook, consider the oldest Excel version its readers use. A formula that works in Microsoft 365 may not be recognized in an older perpetual release. If portability is essential, test the workbook in the target version and select the formula pattern accordingly.

Frequently Asked Questions

Does Excel have a MINIF function?

No. Excel does not provide a built-in function named MINIF. Use MINIFS for a conditional minimum in supported versions, or use the MIN(IF(...)) array formula pattern where needed.

What is the simplest formula for a minimum that meets one condition?

Use MINIFS with the values range first, then the condition range and criterion. For example, if B2:B7 contains regions and C2:C7 contains values, a criterion of East returns the smallest value associated with East.

Why does MINIFS return zero when nothing matches?

Zero is the result MINIFS returns when no values satisfy the criteria. Use a separate match count, such as COUNTIFS, to tell that situation apart from a genuine minimum of zero.

Can MINIFS check two or more conditions?

Yes. Add a criteria-range and criterion pair for each additional condition. Excel requires all the supplied conditions to be true for a row to qualify, so the pairs work as AND conditions.

Do I need Ctrl+Shift+Enter for MIN IF?

In Microsoft 365, you can normally enter MIN(IF(...)) with Enter. Older Excel versions that require traditional array entry need Ctrl+Shift+Enter on Windows; on Mac, use Control+Shift+Return. MINIFS does not require array entry.

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.