Description
Use this function to return the minimum value among cells specified by a given set of conditions or criteria.
MINIFS is useful for finding the smallest value in a range that meets one or more specific conditions. It's particularly helpful when analyzing data with multiple variables or categories.
Syntax
MINIFS(min_range, criteria_range1, criteria1, […, criteria_range_126, criteria_126])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
min_range |
Yes | The range of cells from which you want to determine the minimum. | A cell range containing numbers, or a formula which results in this. |
criteria_range_1 |
Yes | The range that is tested using criteria1. | A cell range containing numbers, or a formula which results in this. |
criterion_1 |
Yes | The criteria used to determine which cells in the min_range to evaluate. |
A number, expression, cell reference, text, or function that defines which cells will be evaluated. |
[criteria_range_n, criterion_n] |
No | Additional ranges and their corresponding criteria. | Same as criteria_range_1 and criterion_1. Up to 126 additional range/criteria pairs can be provided. |
Example
Sample data
| A | B | C | D | |
|---|---|---|---|---|
1 |
Product | Category | Region | Sales |
2 |
Apple | Fruit | North | 120 |
3 |
Banana | Fruit | South | 85 |
4 |
Carrot | Vegetable | East | 95 |
5 |
Date | Fruit | West | 110 |
6 |
Eggplant | Vegetable | North | 75 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the lowest value in a range that matches a single category. | =MINIFS(D2:D7, B2:B7, "Fruit") |
Finds the lowest sales figure among products categorized as "Fruit" (Apple, Banana, Date). Result: 85 |
| Find the lowest value in a range that matches a single condition. | =MINIFS(D2:D7, C2:C7, "North") |
Finds the lowest sales figure among products sold in the "North" region (Apple, Eggplant). Result: 75 |
| Find the lowest value in a range that matches two conditions at once. | =MINIFS(D2:D7, B2:B7, "Vegetable", C2:C7, "North") |
Finds the lowest sales figure among products that are both "Vegetable" and sold in the "North" region — only Eggplant qualifies. Result: 75 |
| Find the lowest value in a range that itself meets a numeric condition. | =MINIFS(D2:D7, D2:D7, ">100") |
Finds the lowest sales figure among values already greater than 100 (Apple, Date). Result: 110 |
| Find the lowest value in a range that matches one condition while excluding a specific item. | =MINIFS(D2:D7, A2:A7, "<>Apple", B2:B7, "Fruit") |
Finds the lowest sales figure among "Fruit" products, excluding Apple (leaving Banana and Date). Result: 85 |
Notes
- The
min_rangeand allcriteria_rangesmust be the same size and shape. - If no cells in the
min_rangemeet all the criteria, MINIFS returns 0. - MINIFS ignores case when comparing text values.
- MINIFS ignores blank cells.
- Use operators (>, <, <>) in your criteria for more flexible conditions.
- You can use wildcard characters (* and ?) in criteria when searching for text.
Tips
- Use MINIFS to find the lowest value that meets multiple conditions in your data.
- Combine MINIFS with other functions such as MAXIFS for comprehensive data analysis.
- When working with dates, use DATE in your criteria for precise filtering.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA