Excel - MINIFS Function
MINIFS Function
The Excel MINIFS function identifies the minimum value among a certain set of cells only when the criteria are TRUE in the other criteria range. The hidden qualities of the MINIFS function are that it ignores hidden rows, is efficiently adequate with the date and time, permits users to write multiple criteria, logical operators and wildcards(* and ?) can be utilized in the criteria. For example, determining the minimum salary of the employee of the Manufacturing department, and identifying the minimum temperature of the city in the North region.
Compatibility
This advanced Excel function is compatible with the following versions of MS Excel
- Excel for Microsoft 365
- Excel for Microsoft 365 for Mac
- Excel for the web
- Excel 2024
- Excel 2024 for Mac
- Excel 2021
- Excel 2021 for Mac
- Excel 2019
- Excel 2016
Syntax
The syntax of the MINIFS function is as follows
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments
You can use the following arguments with the MINIFS function −
| Argument | Description | Required/ Optional |
|---|---|---|
| min_range | It specifies the actual range of cells to extract the minimum value. | Required |
| criteria_range1 | A range of cells to compute the criteria. | Required |
| criteria1 | It denotes the specific condition in the form of a numeric value, expression, or text that would apply on the criteria_range1. | Required |
| criteria_range2, criteria2, ... | Additional criteria_range and their associated criteria. You can define upto 126 range/criteria pairs. | Optional |
Points to Remember
- This remarkable function was introduced in Excel 2016 and is not supported in the prior version of Microsoft Excel.
- The dimension of the min_range and criteria_rangeN arguments must be the same, otherwise, the MINIFS function returns the #VALUE! Error.
- If the condition doesn't match the criteria range of cells, then the MINIFS function returns the zero function.
- The logical operators(=,>, <,<>) can be straightforwardly employed in the criteria argument.
Examples of MINIFS Function
Practice the following examples to learn the use of the MINIFS function in Excel.
Example 1: How to Calculate the Minimum Marks of Students having A+ Grade
In this example, we will demonstrate the MINIFS function to extract the lowest marks that have A+ grades.
Solution
Step 1 − Assume the sample dataset consists of the Marks and Grade column and their associated data value are specified in the cell range B3:B10 and C3:C10. You may write the formula =MINIFS(B2:B9,C2:C9,"A+") and press the Enter tab.
Therefore, the MINIFS function retrieves the lowest marks 75 of A+ grade.
Example 2: How to use the Multiple Criteria in the MINIFS function
In this example, you will identify the minimum sales production where the Sales Executive is "Joseph" and the Region is "Europe".
Solution
Step 1 − Navigate to the Sheet2 and enter the formula =MINIFS(C2:C7,A2:A7,"Joseph",B2:B7,"Europe") in the D2 cell. In this example, the minimum value is extracted from the cell range C2:C7 only when the Joseph is found in the criteria range A2:A7 and Region Europe is existed in the cell B2:B7.
Hence, the minimum sales production is 19,000.
Download Practice Sheet
You can download and use the sample data sheet to practice the MINIFS function.