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 |
|---|---|---|---|
| Product | Category | Region | Sales |
| Apple | Fruit | North | 120 |
| Banana | Fruit | South | 85 |
| Carrot | Vegetable | East | 95 |
| Date | Fruit | West | 110 |
| Eggplant | Vegetable | North | 75 |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=MINIFS(D2:D7, B2:B7, "Fruit") |
Find the minimum sales for Fruits. | 85 |
=MINIFS(D2:D7, C2:C7, "North") |
Find the minimum sales in the North region. | 75 |
=MINIFS(D2:D7, B2:B7, "Vegetable", C2:C7, "North") |
Find the minimum sales for Vegetables in the North region. | 75 |
=MINIFS(D2:D7, D2:D7, ">100") |
Find the minimum sales value that is greater than 100. | 110 |
=MINIFS(D2:D7, A2:A7, "<>Apple", B2:B7, "Fruit") |
Find the minimum sales for Fruits, excluding Apples. | 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.
- 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