MINIFS Function. Get the minimum value in a cell that matches multiple criteria.(Microsoft Excel)

03/28/2024

Japanese version.

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).

The MINIFS function does not exist in versions prior to Excel 2016, so intermediate cells using the IF function must be created or array formula must be used.

How it works

=MINIFS(Min_range,Criteria1, Criteria_range2, Criteria2 ... Criteria_range127, Criteria127)
NameOmissionDescription
Min_rangeRequired argument.Specify the target cell range.
Criteria_range1 - 127Only 1 is required.Specifies a range of cells to be evaluated for the criteria.
Criteria1 - 127Only 1 is required.Specify the criteria to be included in the tally.

Demonstrate

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

=MINIFS($G$4:$G$11,$D$4:$D$11,J4,$H$4:$H$11,TRUE)

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.

=MINIFS($B$2:$B$10,$A$2:$A$10,"Pen*") 
=MINIFS($B$2:$B$10,$A$2:$A$10,"*book*") 
=MINIFS($B$2:$B$10,$A$2:$A$10,"*C")

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.

=MINIFS(G4:G11,E4:E11,">="&N4,E4:E11,"<"&O4)
=MINIFS(G4:G11,E4:E11,">=150",E4:E11,"<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.

Spill

If you specify the criteria as a range of cells, it will be a Spill.

Also, Spill when using the BYROW or BYCOL function.

=BYROW(A1:C3,LAMBDA(r,MINIFS(r,r,">=4",r,"<=7")))
Row direction
=BYCOL(A1:C3,LAMBDA(c,MINIFS(c,c,">=4",c,"<=7")))
Column direction

---

Links

Maximum/minimum values for multiple values or cell ranges.

FunctionImportanceDescription
MINHighGet the smallest number.
MAXHighGet the largest number.
MINAMiddleGet the minimum value, including spaces and boolean values.
MAXAMiddleGet the maximum value, including spaces and boolean values.
MINIFSHighGet the minimum value in a cell that matches multiple criteria.
MAXIFSHighGet the maximum value in a cell that matches multiple criteria.

Microsoft Excel Functions Statistical