Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)®. Register today!

MINIFS Function

Returns the smallest numeric value that meets one or more criteria in a given range of values

What is the MINIFS Function?

The MINIFS function is categorized under Statistical functions. It will return the smallest numeric value that meets one or more criteria in a given range of values.

In financial analysis, the MINIFS function can be used to extract the minimum numeric value. For example, we can get the minimum value in a test scored by a male and a female.

 

Formula

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

 

The MINIFS function uses the following arguments:

  1. Min_range (required argument) – It is the actual range of cells in which the minimum value will be determined.
  2. Criteria_range1 (required argument) – It is the set of cells to evaluate with the criteria.
  3. Criteria1 (required argument) – It 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) – It is the additional arrays 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.

 

How to use the MINIFS Function in Excel?

To understand the uses of the MINIFS function, let us consider an example:

 

Example 1

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

 

 MINIFS Function

 

The formula to be used to find the sales figures for product A in Q2 would be:

 

 MINIFS Function - Example 1

 

We get the result below:

 

 MINIFS Function - Example 1a

 

Few notes about the MINIFS Function:

  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.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

  • Advanced Excel Course
  • Advanced Excel Formulas To Know
  • Excel Shortcuts for Windows and Mac
  • Financial Modeling Certification Program

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!