This function prints the smallest value in a cell that satisfies multiple criteria; it is an extension of the MIN function.
Unlike the COUNT function and other functions, there is no single criteria function (MINIF).
- 1. How it works
- 2. Demonstrate
- 3. Spill
- 4. Links
How it works
=MINIFS(Min_range,Criteria1, Criteria_range2, Criteria2 ... Criteria_range127, Criteria127)
|Min_range||Required argument.||Specify the target cell range.|
|Criteria_range1 - 127||Only 1 is required.||Specifies a range of cells to be evaluated for the criteria.|
|Criteria1 - 127||Only 1 is required.||Specify the criteria to be included in the tally.|
For example, it can be used to obtain the minimum value of a specific product category and the minimum value of a product that is eligible for a discount.
It can also be used when a comparison of large and small values is required, such as the minimum value among products with a specific unit price or higher.
An example of obtaining the smallest value among the matches for a specific string.
The following is an example of obtaining the smallest amount of a product that matches the category and is eligible for the discount.
In K4, specify the MINIFS function as follows
The criteria_range is specified by absolute cell reference, because the reference point does not change even if the position is changed by copying.
Example of using wildcards
Forward, partial, and backward matching can also be achieved using wildcards.
Example of retrieval by setting a numerical threshold
The following example searches for the minimum value of Amount in a range where Price is between 150 and 300.
Comparison operators can be specified for search conditions, such as above or below a specific number.
Comparison operators must be enclosed in double-cotation marks and treated as character strings.
If you specify the criteria as a range of cells, it will be a Spill.
Maximum/minimum values for multiple values or cell ranges.
|MIN||High||Get the smallest number.|
|MAX||High||Get the largest number.|
|MINA||Middle||Get the minimum value, including spaces and boolean values.|
|MAXA||Middle||Get the maximum value, including spaces and boolean values.|
|MINIFS||High||Get the minimum value in a cell that matches multiple criteria.|
|MAXIFS||High||Get the maximum value in a cell that matches multiple criteria.|