How to use MINIFS Function in Excel

The Excel MINIFS function returns the smallest numeric value that meets one or more criteria in a range of values. MINIFS can be used with criteria based on dates, numbers, text, and other conditions. MINIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Syntax:= MINIFS (min_range, range1, criteria1, [range2], [criteria2], …)

The minifs function syntax has the following arguments:

  1. Min_range (required argument) – This is the actual range of cells in which the minimum value will be determined.
  2. Criteria_range1 (required argument) – The set of cells to evaluate with the criteria.
  3. Criteria1 (required argument) – This is the criteria in the form of a number, expression, or text that defines which cells will be evaluated as the minimum.
  4. Criteria_range2, criteria2, … (optional arguments) – This is the additional array(s) of values to be tested and the respective conditions to test.MINIFS supports logical operators (>, <, <>, =) and wildcards (*, ?) for partial matching. It can be used with criteria based on dates, numbers, text, and other conditions.

Example: Let’s look at some Excel minifs function examples and explore how to use the minifs function as a worksheet function in Microsoft Excel:

Example 1:

So, here I have a table of some data. The second column contains the numeric values. First column contains the state.

The task is to tell the min value from the second range for each state.

Syntax:  =MINIFS($B$2:$B$21;$A$2:$A$21;D2)

Result: 7950

Based on the Excel spreadsheet above, the following minifs examples would return:

Syntax: =MINIFS($B$2:$B$21;$A$2:$A$21;D2)
Result: 7950

Syntax: =MINIFS($B$2:$B$21;$A$2:$A$21;D3)
Result: 7910

Syntax: =MINIFS($B$2:$B$21;$A$2:$A$21;D4)
Result: 7780

Syntax: =MINIFS($B$2:$B$21;$A$2:$A$21;D5)
Result: 8250

Example 2:

Suppose we are given the sales figure for 3 products from 3 regions, as shown below:

The formula to be used to find the sales figures for product robot would be:

Syntax: =MINIFS(D2:D14;C2:C14;”Robot”)

Result: 75000

Note:

  1. #VALUE! error – Occurs if the min_range and criteria_range arrays are not of equal lengths.
  2. #NAME? error – Occurs when we are using a pre-2016 Excel version that doesn’t support the function.
  3. The MINIFS function is not case-sensitive. For example, when comparing the values in the criteria_range against the criteria, the text strings “TEXT” and “text” will be considered to be a match.
  4. The function can handle up to 126 pairs of criteria_range and criteria arguments.

 

Add a Comment

Your email address will not be published. Required fields are marked *