The MINIFS is a statistics function that returns the minimum value of only the cells that meet certain criteria. In this guide, we’re going to show you how to use the Excel MINIFS function and also go over some tips and error handling methods.


Supported Excel versions

  • Excel 2016 and later

MINIFS Function Syntax

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)


Arguments

min_range

The range of values which will determine the minimum.

criteria_range1

The range of cells that you want to apply the criteria1 against.

criteria1

The criteria that is to be applied to criteria_range1 to define which cells are going to be evaluated for the minimum.

[criteria_range2, criteria2]

Optional. Additional ranges and their associated criteria pairs. You can enter up to 126 range/criteria pairs.



Examples

Note that we’re using named ranges in our sample formulas to make them easier to read. This is not required.

Numeric condition

=MINIFS(Total,Type,”WATER”,Attack,”>=50″,Sp.Atk,”<100″)
formula returns the minimum value from the named range Total, after applying three conditions. All conditions are combined with a logical and operation. As a result, only the values that meet all three criteria are evaluated for the minimum.

String criteria

=MINIFS(HP,Type,”*FIRE*”,Generation,”<>I”)
formula determines the minimum value among the values where the Type contains the “FIRE” string and Generation is not equal to “I”. If we use “FIRE” only without asterisks, the MINIFS function would ignore the “FIRE, GROUND” value.

Download Workbook


Tips

  • Use the same number of rows and columns for min range and criteria range arguments.
    • Bad Example: =MINIFS(G2:G15,F2:H10,”>2014″,J2:J20,”IT”)
    • Good Example: =MINIFS(G2:G11,F2:F11,”>2014″,J2:J11,”IT”)
  • Comparison operators:

Operator

Description

Criteria Sample

Criteria Meaning

=

Equal to

“=10000”

Equal to 10000

<> 

Not equal to

“<>10000”

Not equal to 10000

Greater than

“>10000”

Greater than 10000

Less than

“>10000”

Less than 10000

>=

Greater than or equal to

“>=10000”

Greater than or equal to 10000

<=

Less than or equal to

“<=10000”

Less than or equal to 10000

?

Takes the place of a single character

“Admin?”

6-character word starts by “Admin”

*

Can take the place of any number of characters.

“Admin*”

Any number of character word starts with “Admin”

~

Use tilde in front of a  question mark or an asterisk to actually find them

“Admin~*”

Equal to “Admin*”

Note: Wildcards cannot be used for numeric values. Searching a wild card in a range of numeric values will return no matches.


Issues

#VALUE!

  • The MINIFS function returns incorrect results when you use it to match strings that are longer than 255 characters or to the string #VALUE!.
  • If shape of min_range and criteria_range aren’t the same, MINIFS returns the #VALUE! error.

TRUE and FALSE

TRUE and FALSE values in min_range are evaluated as numbers. While TRUE is evaluated as 1, FALSE is evaluated as 0. As a result this condition may cause unexpected results when they are evaluated as other values.