Excel Find the Minimum Value in a Range With Criteria

How-to basics · Updated November 19, 2026

To find the smallest value in an Excel range that meets a condition, use MINIFS. For example, if prices are in C2:C7 and matching regions are in A2:A7, =MINIFS(C2:C7,A2:A7,"North") returns the lowest price for North; add more range-and-condition pairs to apply multiple criteria.

The Quick Answer: Use MINIFS

MINIFS is the direct, usually simplest solution in Microsoft Excel for Microsoft 365 on Windows. It checks the criteria ranges and returns the smallest number from the corresponding cells in the minimum range. You can enter the formula in an empty cell and press Enter.

With prices in C2:C7 and regions in A2:A7, this formula looks for rows whose region is North:

=MINIFS(C2:C7,A2:A7,"North")

If the North prices are 18, 9, 15, and 22, the result is 9. The function does not rearrange or change the source data; it calculates a result in the cell where you enter the formula. It is useful for finding the lowest sale in a category, the earliest date associated with a label, or the smallest measurement that meets specified conditions.

How MINIFS Arguments Work

The function follows this pattern: MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The first argument is the range containing the values to minimize. Each later pair tells Excel which cells to check and what condition those cells must meet. You can add pairs for additional conditions.

  • min_range: The numbers from which Excel finds the minimum.
  • criteria_range1: The cells Excel checks for the first condition.
  • criteria1: The value, text, cell reference, or expression that defines that condition.
  • Additional pairs: Optional criteria ranges and conditions. A row must meet every supplied condition to qualify.

For a reliable result, make the minimum range and every criteria range the same size and shape. For instance, C2:C100 can be paired with A2:A100, but not with A2:A99. Keeping all ranges aligned also makes formulas easier to inspect and maintain when rows are added or removed.

Worked Example: Find the Lowest Price by Region

Suppose a small sales list has regions in A2:A7, products in B2:B7, and prices in C2:C7:

  • North, Tea, 18
  • South, Tea, 12
  • North, Coffee, 9
  • North, Coffee, 15
  • South, Tea, 7
  • North, Tea, 22

With prices in C2:C7 and regions in A2:A7, this formula returns the smallest price for North:

=MINIFS(C2:C7,A2:A7,"North")

The answer is 9, because 9 is the lowest of the four prices on North rows. To find the lowest North price for Tea specifically, add the product range and condition. With products in B2:B7 as well, enter:

=MINIFS(C2:C7,A2:A7,"North",B2:B7,"Tea")

The answer is 18: only the first and last rows meet both conditions, and the lower of 18 and 22 is 18. This illustrates an important point: multiple criteria in MINIFS are joined with AND. A row qualifies only when it satisfies the region condition and the product condition.

Use Cell References and Comparison Criteria

Typing a fixed condition such as "North" works, but a reference makes the result easier to change. If E2 contains North, and regions are in A2:A7 while values to minimize are in C2:C7, use:

=MINIFS(C2:C7,A2:A7,E2)

Changing E2 to South updates the result without editing the formula. Criteria can also use comparison operators such as greater than, less than, or not equal to. Operators are written as text, and when an operator is joined to a cell reference, use the ampersand to combine them. With regions in A2:A7, prices in C2:C7, North in E2, and a threshold in F2, this formula finds the lowest North price above the threshold:

=MINIFS(C2:C7,A2:A7,E2,C2:C7,">"&F2)

If F2 contains 10, the North values above 10 are 18, 15, and 22, so the result is 15. To include the threshold itself, use ">="&F2 instead. This approach works for numeric thresholds and, with valid Excel date values, date boundaries too.

Text, Wildcards, and OR Conditions

A text criterion normally matches the cell content you specify. For a partial text match, MINIFS supports the wildcards * for any sequence of characters and ? for one character. A tilde (~) can be used to search for a literal wildcard character. For example, if product names are in B2:B7 and values to minimize are in C2:C7, this finds the smallest value for products whose names start with Tea:

=MINIFS(C2:C7,B2:B7,"Tea*")

Use wildcards carefully: a partial match may include more rows than intended. A condition of "Tea*" could match Tea, Teapot, or Tea bags. If you need one condition or another—for example, North or South—MINIFS alone does not express that OR relationship as a single criteria pair. In Microsoft 365, FILTER can return the qualifying values and MIN can find their smallest value. With regions in A2:A7 and values in C2:C7, use:

=MIN(FILTER(C2:C7,(A2:A7="North")+(A2:A7="South")))

The plus sign combines the two tests as an OR: a row qualifies if either test is true. FILTER is available in Microsoft 365 and Excel 2021 or later. If no row matches, FILTER returns an error unless you provide its optional if_empty argument; decide how you want a no-match result displayed before relying on this formula in a report.

Find a Minimum Within a Date Period

Excel stores valid dates as numbers, so MINIFS can find the earliest date that meets a condition, or the minimum measurement recorded during a date period. For example, assume dates are in A2:A100, sales amounts are in B2:B100, and the start and end dates are in E2 and F2. To find the lowest sale from the start date through the end date, use:

=MINIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2)

The repeated date range supplies two conditions: the date must be on or after E2 and on or before F2. This includes both endpoints. Make sure E2 and F2 contain actual Excel dates rather than text that merely looks like a date, and check that the source date column contains valid dates. If the result is displayed as a date or an unexpected serial number, adjust the result cell’s number format; formatting changes how a value appears, not the underlying calculation.

No Match, Zero, and Blank Values

A common surprise is that MINIFS returns 0 when no cells meet the criteria. Zero can also be a genuine minimum, so the result alone does not always tell you whether matching rows were found. If a zero would be ambiguous, check the matching rows separately, show a message when there are none, or use a reporting design that distinguishes “no match” from a true zero.

For example, with regions in A2:A7, this count checks how many rows match North:

=COUNTIF(A2:A7,"North")

A count greater than zero confirms that matching region labels exist, but it does not prove those rows contain numeric prices. MINIFS ignores text and blank cells in the minimum range, so a matching row with a blank price is not a numeric minimum. If the matching prices may be stored as text, convert them to numbers before interpreting the result. Also check for spaces or inconsistent labels, such as North followed by a trailing space, which can prevent an exact text condition from matching as expected.

When a formula returns 0 unexpectedly, check the criteria spelling, the alignment and size of the ranges, the data type of the values, and whether any qualifying numeric value is actually zero. These checks are more useful than assuming every zero means the formula failed.

Excel Versions and Alternatives

MINIFS is available in Microsoft 365 and current desktop versions of Excel, including Excel 2019 and later. If a workbook must be used in an older Excel version that does not recognize MINIFS, a traditional array formula can calculate a conditional minimum. With regions in A2:A7 and numeric values in C2:C7, the formula is:

=MIN(IF(A2:A7="North",C2:C7))

In Microsoft 365, enter it normally with Enter. In older Excel versions that require legacy array entry, select the formula cell, type the formula, and press Ctrl+Shift+Enter instead of Enter. Excel displays braces around a legacy array formula in the formula bar; do not type those braces yourself. This alternative is less convenient to maintain, so use MINIFS when compatibility permits.

Another option is a helper column that marks rows meeting the condition, followed by a minimum calculation over qualifying values. A helper column can make complex logic visible to colleagues and easier to audit, though it adds an extra step and another range to maintain. For a simple category or date condition, MINIFS is generally clearer. For OR logic or a list of conditions that changes dynamically, FILTER can be a more flexible alternative in supported versions.

Common Troubleshooting Checks

If the answer does not look right, verify the formula and the source data before rebuilding the workbook. These checks cover many typical causes:

  • Confirm that the minimum range contains the values you intend to compare and that all ranges cover the same rows.
  • Check whether numbers and dates are stored as numeric values rather than text.
  • Inspect labels for extra spaces, inconsistent spelling, or different punctuation.
  • Review every condition pair: MINIFS requires all supplied conditions to be true for a row to qualify.
  • Check whether the criteria use the intended boundary, such as greater than > versus greater than or equal to >=.
  • Remember that changing the cell’s number format changes its display, not the minimum calculation.

For a large worksheet, consider turning the data into an Excel table so formulas can use named columns and expand as rows are added. A table is optional; ordinary cell ranges work well when their boundaries are maintained. Whichever layout you choose, keep the data organized with one record per row and a clear header for each field. That structure makes conditional calculations easier to verify and update.

Frequently Asked Questions

Can MINIFS use more than one condition?
Yes. Add a criteria range and condition for each extra requirement. The ranges must align, and all supplied conditions must be true for a row to count.

Why does MINIFS return zero when no value matches?
Zero is the function’s result when no cells meet the conditions. Since zero can also be a real minimum, check for matching records separately if the distinction matters.

Can MINIFS find the earliest date that matches a condition?
Yes. Excel dates are numeric values, so MINIFS can return the earliest qualifying date. Apply a date number format to the result cell if it appears as a serial number.

How do I find a minimum for either of two categories?
MINIFS applies multiple criteria as AND, not OR. In Microsoft 365 or Excel 2021 and later, use FILTER with combined tests and MIN, and account for the possibility that no rows match.

Does MINIFS include blank cells as zero?
Blank cells in the minimum range are ignored rather than treated as numeric zeros. A real numeric zero can still be the minimum, and text numbers may need conversion before the result is meaningful.

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.