MINIFS

Excel Functions

Get minimum value with criteria

Syntax

EXCEL

=MINIFS(min_range, range1, criteria1, [range2], [criteria2], ...)

Arguments

Parameter	Description
`min_range`	Range of values used to determine minimum.
`range1`	The first range to evaluate.
`criteria1`	The criteria to use on range1.
`range2`	[optional] The second range to evaluate.
`criteria2`	[optional] The criteria to use on range2.

Return Value

Minimum value

Details

The MINIFS function returns the smallest numeric value in cells that meet multiple conditions, referred to as criteria. Each condition is provided with a separate range and criteria. To define criteria, MINIFS supports various logical operators (>,<,<>,=) and wildcards (*,?,~). The syntax used to apply criteria in MINIFS is a bit tricky because it is unusual in Excel. See below for details. The syntax for the MINIFS function depends on the criteria being evaluated. Each condition is provided with a separate range and criteria. The generic syntax for MINIFS looks like this: The MINIFS function takes three required arguments: min_range, range1, and criteria1. With these three arguments, MINIFS returns the minimum number in min_range where corresponding cells in range1 meet the condition set

Examples

Syntax

The syntax for the MINIFS function depends on the criteria being evaluated. Each condition is provided with a separate range and criteria. The generic

EXCEL

=MINIFS(min_range,range1,criteria1) // 1 condition
=MINIFS(min_range,range1,criteria1,range2,criteria2) // 2 conditions

Basic example

In the worksheet shown above, the formulas in G5 and G6 are:

EXCEL

=MINIFS(D5:D16,C5:C16,"F") // returns 72
=MINIFS(D5:D16,C5:C16,"M") // returns 64

Two criteria

The formulas in H5:I6 are:

EXCEL

=MINIFS(D5:D16,C5:C16,"F",E5:E16,"A") // returns 72
=MINIFS(D5:D16,C5:C16,"F",E5:E16,"B") // returns 83
=MINIFS(D5:D16,C5:C16,"M",E5:E16,"A") // returns 65
=MINIFS(D5:D16,C5:C16,"M",E5:E16,"B") // returns 64

Other criteria

To return the minimum value in A1:A100 when cells in B1:B100 are greater than 50:

EXCEL

=MINIFS(A1:A100,B1:B100,">50")

Other criteria

To get the minimum value in A1:A100 when cells in B1:B100 are less than or equal to 100, and cells in C1:C100 are greater than zero:

EXCEL

=MINIFS(A1:A100,B1:B100,"<=100",C1:C100,">0")

Not equal to

To construct "not equal to" criteria, use the "" operator surrounded by double quotes (""). For example, to return the minimum value in A1:A100 when

EXCEL

=MINIFS(A1:A100,B1:B100,"<>red")

MINIFS

Syntax

Arguments

Return Value

Details

Examples

See Also