Excel Find Minimum Value Based on Criteria in a Range

How-to basics · Updated November 19, 2026

To find the smallest value that meets one or more conditions, use Excel’s MINIFS function. With regions in A2:A5, products in B2:B5, values in C2:C5, and the requested region and product in E2 and F2, enter =MINIFS(C2:C5,A2:A5,E2,B2:B5,F2); if the matching values are 12 and 8, the result is 8.

How MINIFS Finds a Conditional Minimum

MINIFS checks one or more criteria ranges, keeps the values in the minimum range whose corresponding rows satisfy every condition, and returns the smallest of those values. The arguments come in pairs after the minimum range: a criteria range followed by the criterion to apply to it. You can add more pairs when a result must meet additional conditions.

For example, suppose a small sales table has these rows: East, Apples, 12; East, Apples, 8; East, Oranges, 5; and West, Apples, 6. The first formula matches only rows where the region is East and the product is Apples, so it compares 12 with 8 and returns 8. It does not select 5 or 6 because those rows fail at least one condition.

MINIFS is available in Microsoft 365 and Excel 2019 or later, including Excel 2021 and Excel 2024. The formula works in current Excel for Windows, Mac, and the web; the function name and comma-separated syntax are the same. If your regional settings use semicolons as formula separators, Excel may display or require semicolons instead of commas.

Set Up the Data and Criteria

Keep the values you want to minimize in one column, and put each condition in its own column. Each record should occupy one row, with its value and all related details aligned across that row. A clear layout might use column A for region, column B for product, and column C for amount. Put the requested region in E2 and the requested product in F2.

With those headers and records in rows 2 through 5, use =MINIFS(C2:C5,A2:A5,E2,B2:B5,F2). For the example records above, and with E2 set to East and F2 set to Apples, the answer is 8. The value range and each criteria range cover the same rows, which is essential: the first value in each range must describe the same record, the second values must describe another record, and so on.

For a larger table, extend all ranges to cover the same last row. Avoid using mismatched ranges such as C2:C100 with A2:A99; Excel requires the minimum range and criteria ranges to have compatible dimensions and alignment. If the data grows frequently, an Excel Table can make formulas easier to maintain because structured references expand as rows are added.

Use Comparisons, Dates, and Text Criteria

Criteria can be exact values, text, numbers, cell references, or comparison expressions. Put comparison operators such as greater-than or less-than inside quotation marks, and join the operator to a referenced value with &. This lets a criterion change when the value in a worksheet cell changes.

With amounts in C2:C6 and categories in A2:A6, suppose the category is “Travel” on rows with amounts 0, 24, 15, 9, and 31, and the category column identifies which amounts qualify. To find the smallest positive Travel amount, with E2 containing Travel, use =MINIFS(C2:C6,A2:A6,E2,C2:C6,">0"). Among qualifying positive amounts 24, 15, 9, and 31, the result is 9; the zero is excluded.

For dates, Excel stores real dates as serial numbers, so date comparisons work when the cells contain valid Excel dates rather than text that merely looks like a date. With transaction dates in A2:A20, amounts in B2:B20, the start date in E2, and the first day of the following month in F2, use =MINIFS(B2:B20,A2:A20,">="&E2,A2:A20,"<"&F2). If the amounts in that date interval are 14, 9, and 11, the result is 9. The less-than end boundary includes dates before F2 while excluding F2 itself, which is useful when dates may also contain times.

For text patterns, Excel criteria support wildcards: an asterisk matches any number of characters, and a question mark matches one character. With amounts in C2:C20 and product names in B2:B20, use =MINIFS(C2:C20,B2:B20,"North*") to find the minimum for product names beginning with North. A tilde can escape a wildcard when you need to match a literal asterisk or question mark.

What Happens When No Rows Match?

A common surprise is that MINIFS returns 0 when no cells meet the criteria. That zero does not prove that a qualifying record has an actual value of zero; it can mean there were no matches. This matters when zero is a valid measurement, price, score, or balance, because the result alone cannot distinguish the two cases.

Check whether a matching record exists separately when that distinction matters. With regions in A2:A100 and the requested region in E2, use =COUNTIF(A2:A100,E2) to count matching rows. If the count is zero, there are no rows for that region; if it is positive, a zero returned by the minimum formula may be a real minimum. This check counts region matches only, so include additional criteria in the count if the minimum uses multiple conditions.

Also consider whether blank cells in the value column should count as missing data or whether you intend to treat them as zero. A blank is not the same thing as a numeric zero. Clean imported data and confirm that amounts and dates are stored as numbers or dates; values stored as text may not behave as expected in comparisons or minimum calculations.

Older Excel: Use an Array Formula

If the workbook must run in a version without MINIFS, an array formula can apply the conditions and then take the minimum. With regions in A2:A5, products in B2:B5, values in C2:C5, and criteria in E2 and F2, use =MIN(IF((A2:A5=E2)*(B2:B5=F2),C2:C5)). For the example data and criteria East and Apples, it returns 8.

In Excel versions with dynamic arrays, including Microsoft 365 and Excel 2021 or later, press Enter. In older versions that do not support dynamic arrays, confirm this formula with Ctrl+Shift+Enter on Windows rather than Enter alone; Excel displays braces around the formula in the formula bar when it has been entered as a legacy array formula. On Mac, the array-entry shortcut is Control+Shift+Return. Do not type the braces yourself.

This approach can be less convenient than MINIFS: it is easier to make mistakes in array entry, and older Excel may require a fixed range. Use it when compatibility requires it, rather than replacing a straightforward MINIFS formula in a current workbook.

Choose Between MINIFS and Other Approaches

For most conditional minimum calculations, MINIFS is the clearest option because its arguments explicitly pair each condition with a range. Use it when you need the smallest value across ordinary row-based conditions, such as the lowest price for a product in a region or the earliest date for a customer.

  • Use MINIFS for one or several conditions across aligned worksheet ranges.
  • Use the array formula approach when supporting older Excel versions without MINIFS.
  • Use a filter or PivotTable when you also need to inspect the matching records, summarize several measures, or explore the data interactively.

A PivotTable is helpful for reporting repeated summaries by category, but it may take more setup than a single formula. Filtering a table makes the qualifying rows visible, yet it does not by itself create a reusable conditional-minimum result. The right choice depends on whether you need one live answer, an auditable list of matching records, or a broader summary.

Common Formula Problems to Check

If the result is unexpected, first check the ranges. The minimum range and every criteria range must correspond row by row, and each condition should point to the column that actually contains that field. A formula can be syntactically valid but return the wrong answer if, for instance, the product criterion is accidentally applied to the region column.

  • Confirm that text criteria match the source values, including spaces and spelling. Imported data may contain trailing spaces or inconsistent labels.
  • Check that numeric values are numbers, not text. A cell that looks like 12 can be stored as text after an import.
  • For dates, verify that the cells are real Excel dates and that the start and end boundaries are in the intended order.
  • Remember that all criteria in one MINIFS formula must be true for a row to qualify. The criteria are combined as AND conditions, not alternatives.
  • When the answer is 0, determine whether a matching record has a real zero or whether no record matched at all.

To match either of two categories, one MINIFS formula with both categories as criteria will not work as an OR condition. You can calculate the two conditional minima separately and compare them, but account for the no-match behavior before taking the smaller result. Alternatively, add a helper column that assigns a common label to the categories you want grouped, then use that label as the criterion.

Frequently Asked Questions

Can MINIFS use more than one condition?
Yes. Add a criteria-range and criteria pair for every additional condition. A row is included only when it satisfies all the supplied conditions.

Why does MINIFS return zero when there is no match?
Excel returns 0 when no cells satisfy the criteria. Use a separate count or other match check if zero could also be a genuine minimum.

Can I find the minimum for a date range?
Yes. Apply a greater-than-or-equal-to condition for the start date and a less-than condition for the next boundary. Ensure the source dates are stored as Excel dates.

Does MINIFS ignore blank cells?
Blank cells in the minimum range are not numeric minimum values. If blanks or text values cause an unexpected result, inspect the source data and decide how missing values should be handled.

What can I use if my Excel version does not have MINIFS?
Use an array formula with MIN and IF. In dynamic-array versions, press Enter; in older Excel, confirm it with Ctrl+Shift+Enter on Windows or Control+Shift+Return on Mac.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.