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.
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The MINIFS function uses the following arguments:
- Min_range (required argument) – It is the actual range of cells in which the minimum value will be determined.
- Criteria_range1 (required argument) – It is the set of cells to evaluate with the criteria.
- 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.
- 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:
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 A in Q2 would be:
We get the result below:
Few notes about the MINIFS Function:
- #VALUE! error – Occurs if the min_range and criteria_range arrays are not of equal lengths.
- #NAME? error – Occurs when we are using a pre-2016 Excel version that doesn’t support the function.
- 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.
- The function can handle up to 126 pairs of criteria_range and criteria arguments.
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: