Excel MIN With Criteria: Formulas, Examples, and Fixes

Formulas & functions · Updated November 18, 2026

To find the smallest value in Excel that meets a condition, use MINIFS: =MINIFS(B2:B10,A2:A10,"East"). It returns the minimum from B2:B10 only for rows where the corresponding cell in A2:A10 is East; add more criteria pairs to narrow the result further.

The quickest way to find a minimum by condition

MINIFS is usually the clearest solution in Microsoft Excel for Microsoft 365 on Windows. Its syntax is MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The first argument is the set of values Excel should minimize. Each following pair tells Excel where to test a condition and what that condition is.

For example, suppose A2:A7 contains regions and B2:B7 contains order amounts: East 18, West 12, East 9, East 14, West 7, and East 11. With regions in A2:A7 and amounts in B2:B7, =MINIFS(B2:B7,A2:A7,"East") returns 9, the smallest amount belonging to East. The order of the rows does not matter, but the ranges must line up: A2 is tested against B2, A3 against B3, and so on.

MINIFS is available in Microsoft 365 and Excel 2019 or later. If a workbook must work in an older Excel version, use an array formula or another approach described below.

How MINIFS evaluates its ranges

MINIFS checks each row against every condition supplied. A row contributes its value from min_range only when it satisfies all the criteria. This is an AND relationship: a row must pass criterion one and criterion two and any additional criteria. The criteria ranges should be the same size and shape as the minimum range. Keeping all three ranges aligned to the same rows prevents mismatched results and is easier to audit.

Criteria can be literal text, a number, a comparison expression, a wildcard pattern, or a reference to a cell containing a criterion. Text comparisons are generally case-insensitive, so East and EAST match the same text for this purpose. MINIFS evaluates numeric values as numbers; a number stored as text may not behave like a true numeric value in the minimum range.

The function returns 0 when no cells meet its criteria. That can be confusing if zero is also a valid minimum, so consider adding a separate match check when a report needs to distinguish “no matching records” from a genuine zero.

Using more than one criterion

To require two conditions, add another criteria-range and criteria pair. Suppose A2:A6 contains regions, B2:B6 contains sales representatives, and C2:C6 contains amounts. The records are East/Mina/12, East/Leo/9, West/Mina/6, East/Mina/8, and West/Leo/11. With regions in A2:A6, names in B2:B6, and amounts in C2:C6, =MINIFS(C2:C6,A2:A6,"East",B2:B6,"Mina") returns 8: the lowest amount for Mina in the East region.

Criteria pairs can be added for fields such as product, department, status, or date. For example, a report might ask for the smallest invoice amount for one supplier during a particular month. Be precise about what each range represents: the minimum range contains the result values, while criteria ranges contain the attributes used to select records.

You can also refer to cells instead of typing conditions into the formula. With the same layout and the desired region in E2 and representative in F2, =MINIFS(C2:C6,A2:A6,E2,B2:B6,F2) returns 8 when E2 contains East and F2 contains Mina. This makes a summary formula reusable: change the selector cells rather than editing the formula.

Numbers, comparisons, wildcards, and dates

A criteria argument can express a comparison such as greater than, less than, or not equal to. Put the operator and value together in a text criterion, or join the operator to a cell reference with the ampersand. With regions in A2:A7 and amounts in B2:B7 as in the earlier example, =MINIFS(B2:B7,A2:A7,"East",B2:B7,">=10") returns 11, the smallest East amount of at least 10. The matching amounts are 18, 14, and 11.

For a changeable threshold, put the threshold in E2. With the same data layout, =MINIFS(B2:B7,A2:A7,"East",B2:B7,">="&E2) returns 11 when E2 contains 10. The operator is text, while E2 supplies the comparison value.

For text criteria, Excel supports the wildcard characters ? for one character and * for any number of characters. A tilde (~) can escape a wildcard when you need to search for the literal character. For example, a criterion of North* can match text beginning with North. Wildcards apply to text criteria, not numeric comparisons.

Dates are stored as numbers in Excel, so date limits can be used as comparison criteria. With dates in B2:B20, amounts in C2:C20, and the region in A2:A20, =MINIFS(C2:C20,A2:A20,"East",B2:B20,">="&DATE(2026,1,1),B2:B20,"<"&DATE(2027,1,1)) finds the lowest amount for East with a date in calendar year 2026. The start is inclusive and the next-year boundary is exclusive, which also works when the date cells contain times. The result depends on the workbook’s records; the formula does not assume a particular minimum.

Checking for no matches and handling blanks

Because MINIFS returns zero when no rows qualify, a result of 0 alone does not prove that a matching row exists. For a simple dataset where each matching row has a numeric amount, count the rows meeting the same selection criteria first. With regions in A2:A7 and amounts in B2:B7, =IF(COUNTIFS(A2:A7,"East")=0,"No match",MINIFS(B2:B7,A2:A7,"East")) returns 9 for the example data, and returns No match if no East record exists. COUNTIFS checks the criteria; MINIFS calculates the minimum.

If the minimum range can contain blanks, decide whether blank amounts should be excluded from the calculation. A blank is not a meaningful measurement, and blank-related results can be mistaken for a real zero. One way to exclude blank minimum cells is to add the minimum range as an additional criteria range with a not-blank criterion. With regions in A2:A7 and amounts in B2:B7, =MINIFS(B2:B7,A2:A7,"East",B2:B7,"<>") returns 9 for the example data while requiring the amount cell to be nonblank.

That extra condition excludes empty cells, but it does not make text entries into numbers. If imported amounts look numeric but are stored as text, convert or clean the source data, then check that the cells are recognized as numbers. Also consider whether zero is a valid amount: excluding blanks should not mean excluding legitimate zeros.

Options for older Excel versions

In Excel versions without MINIFS, a conditional minimum can be calculated with an array formula. In Microsoft 365, with regions in A2:A7 and amounts in B2:B7, =MIN(IF(A2:A7="East",B2:B7)) returns 9 for the example data. The IF expression passes amounts from East rows to MIN and supplies FALSE for other rows, which MIN ignores in this array calculation.

In legacy Excel versions that do not support dynamic arrays, confirm this formula with Ctrl+Shift+Enter rather than Enter alone. Excel displays braces around a correctly entered legacy array formula; do not type the braces yourself. In Microsoft 365, press Enter. Array formulas can be harder for other users to understand and maintain, so use MINIFS when the workbook’s required Excel version supports it.

Another option is to filter the records and inspect the remaining values, or use a PivotTable when the goal is to summarize many categories rather than calculate one result. These methods can be useful for interactive analysis, but they do not always provide the same compact, automatically updating formula as MINIFS.

Common errors and ways to troubleshoot

  • All ranges are not aligned. Check that min_range and each criteria range cover corresponding rows and have the same dimensions. A2:A20 and B2:B20 are aligned; A2:A20 and B3:B21 are not.
  • The result is zero unexpectedly. Check whether there are no matching rows, whether blanks are present in the minimum range, and whether the source contains actual numeric values. A zero may also be a genuine minimum.
  • The formula returns an error. Confirm that each criteria range has a criterion after it and that the formula’s parentheses are balanced. In Excel for Windows with standard regional settings, arguments are separated by commas; some regional configurations use semicolons.
  • A comparison criterion does not work. When building the condition from a cell, join the operator and cell reference, such as ">="&E2. Check that the compared values are numbers or real Excel dates rather than text that merely looks numeric.
  • The result differs from a manually filtered list. Verify that the filter and formula use the same conditions, especially spaces in text, date boundaries, and whether blank amounts are intended to count.

On Excel for the web, MINIFS is available in current Microsoft 365 workbooks, though menus and formula entry can look different from the Windows desktop app. On Excel for Mac, the formula itself uses the same syntax; the legacy array-entry shortcut can differ, so consult the Excel for Mac shortcut guidance for the installed version if Ctrl+Shift+Enter is needed.

Frequently Asked Questions

What is the Excel formula for minimum with one condition?
Use MINIFS with the values to minimize first, then the condition range and criterion. For example, =MINIFS(B2:B10,A2:A10,"East") finds the lowest value in B2:B10 where the same-row cell in A2:A10 is East.

Can MINIFS use multiple criteria?
Yes. Add a criteria range and criterion for each additional condition. MINIFS requires all supplied criteria to be true for a row to contribute to the minimum.

Why does MINIFS return zero when there is no match?
Zero is the function’s result when no cells satisfy its criteria. If zero is also a valid measurement, pair MINIFS with a count check so the formula can display a message when no qualifying rows exist.

Does MINIFS work with dates?
Yes. Excel dates are numeric values, so MINIFS can compare a date range with date boundaries. For a month or year, an inclusive start date and exclusive next-period start are often a dependable way to include dates that have times attached.

What can I use instead of MINIFS in older Excel?
Use an array formula such as =MIN(IF(A2:A10="East",B2:B10)). In older Excel versions, confirm it with Ctrl+Shift+Enter; in Microsoft 365, Enter is sufficient. The ranges in this example assume regions in A2:A10 and values in B2:B10.

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.