Buzz
The Minifs function in Excel was first introduced in Excel 2016. Therefore, the Minifs function is not available like the MINA function in previous versions of Excel and is also not available in Excel 2016 for Mac. The Minifs function returns the smallest value in Excel similar to the MINA function, but the usage of these two functions differs.
Syntax of the Minifs function
MINIFS Function Syntax
Function arguments:
min_range: Array of numerical values (or a range of cells containing numerical values) from which you want to return the smallest value if the criteria are met.
criteria_range1: Array of values (or a range of cells containing values) to be evaluated based on criteria1. (This array must be of the same length as min_range).
criteria1: Condition compared against values in criteria_range1.
[criteria_range2, criteria2],
[criteria_range3, criteria3]: Other arrays of values being evaluated and corresponding conditions being checked.
Note:
- The MINIFS function can handle up to 126 range/criteria pairs.
- Each provided criterion can be:
+ A numerical value (which can be an integer, decimal, date, time, or logical value, for example, 5, 01/01/2017, TRUE).
Alternatively:
+ A string of text (e.g., “Address”, “Friday”, …).
Alternatively:
+ An expression (e.g., “> 1”, “<> 0”).
- In criteria related to text, you can use wildcard characters:
? - to match any single character.
* - to match any sequence of characters.
- If the criterion is a string of text or an expression, it must be enclosed in double quotes when provided to the Minifs function.
- Minifs function is not case-sensitive. For example, when comparing values in criteria_range, the text strings “TEXT” and “text” are considered a match.
- Minifs function was first introduced in Excel 2016, hence not available in earlier versions of Excel. Additionally, Minifs function is not available in Excel 2016 for Mac.
Some examples of using MINIFS function in Excel
The spreadsheet below showcases quarterly sales revenue from 3 retail distributors.
Minifs function is employed to find the minimum sales figures across quarters, regions, or sales representatives (or a combination of quarters, regions, and sales representatives).
This is demonstrated in the following examples.
- Example 1:
To find the minimum sales in quarter 1, use the Minifs function:
=MINIFS( D2:D13, A2:A13, 1 )
Resulting in $125,000.
In this example, the Excel Minifs function identifies rows with values in column A equal to 1 and returns the corresponding minimum value in column D.
The Minifs function identifies minimum values as $223,000, $125,000, and $456,000 (from columns D2, D3, and D4).
Example 2:
Furthermore, with the same dataset, we can utilize Minifs function to determine the minimum sales for 'Jeff' during quarters 3 and 4:
In the spreadsheet above, you can use the Maxifs function to calculate the maximum sales for “Jeff” in quarters 3 and 4:
=MINIFS( D2:D13, A2:A13, '>2', C2:C13, 'Jeff' )
This formula returns a result of $310,000.
In this example, the Excel Minifs function identifies rows with: values greater than 2 in column A and entries in column C equal to “Jeff”.
And returns the corresponding minimum value in column D.
The formula above finds minimum values of $310,000 and $261,000 (in columns D8 and D11).
To explore more examples of the Excel Minifs function, readers can visit the Microsoft Office website and find additional reference examples.
Some errors related to Minifs function in Excel 2016
If encountering errors related to the Excel Minifs function, it could be due to:
Common Error:
#VALUE! - Error occurs if the min_range and criteria_range arrays provided have unequal lengths.
#NAME? - Error occurs if you are using an older version of Excel (prior to Excel 2016), which does not support the Minifs function.
Here, Mytour has introduced the MINIFS function in Excel to readers, along with its syntax and usage. To enhance your MINIFS function skills, you can download a sample MIN function exercise and apply this Excel function.
