In this post, you’ll learn about MINIFS Function, its syntax and the way of using MINIFS Function in Microsoft Excel.
What is MINIFS Function?
MINIFS Function in excel is a statistical function. The MINIFS Function calculates and returns the smallest numeric value that meets one or more criteria in a given range.
MINIFS can be used with criteria based on dates, numbers, text, and other conditions. MINIFS supports logical operators(>, <, <>, =) and wildcards(*, ?) for partial matching.
In financial analysis, the MINIFS Function is useful in extracting a minimum numeric value.
Syntax
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)
Arguments
- = – built-in function.
- MINIFS() – function name.
- min_range – range of values used to determine minimum values among the given data.
- range 1(required value),range 2[optional] – the range arguments to calculate.
- criteria 1(required argument), criteria 2[optional] – the criteria to use in range. Criteria can be number, expression or text that defines which cells will be evaluates as a minimum value.
MINIFS Function is in a group of eight functions in Excel that splits logical criteria into two parts(range + criteria).
Notes:
- Conditions applied using range or criteria pairs.
criteria pairs – refers to the word that by a single sound.
Example- fan / van.
- MINIFS can handle up to 126 range or criteria pairs.
- #VALUE Error – if criteria range is not the same size as the min_range.
- MINIFS Function returns 0, if no cells match criteria.
- Ignores empty cells, even when criteria match.
REMARK:
MINIFS Function is a new function, available in Excel 365 and Excel 2019 but in earlier versions you have to use an array formula MIN and IF to find the minimum values with criteria.
How to use MINIFS Function in Excel ?
MINIFS Function calculates smallest numeric values with one or more criteria.
DOUBLE QUOTES (“”):
- Text values are must be within double quotes, but numbers are not. If the logical operator with a number, the number and operator must be within double quotes.
- Value from another cell – value from another cell by using catenation.
- WILLCARDS:
The wildcards characters question mark (?), asterisk (*), or tilde (~) can used in criteria.
- The question mark (?) matches any single characters.
- The asterisk (*) matches any sequences of characters.
- The tilde (~) is an escape character it allows you to find the literal wildcard.
Example:
Using MINIFS Function we can calculate the minimum score of 2nd years among the batch of 1st, 2nd and 3rd year students.
Step 1:
Open the workbook in your Microsoft Excel.
Step 2:
Enter the data, in the workbook.
STEP 3:
In the new cell, give the formula of the function or the syntax. Start with ‘ =’ for every functions, MINIFS to initiate functions name, followed by open parenthesis.
According to the arguments of the syntax, you have to calculate the min_range. Therefore the min_range value is from D5:D14.
According to the example, the min_range is the score of the students.
STEP 4:
After calculating min_range, next step is to calculate criteria_range 1.
criteria_range 1 is from C5:C14.
According to the example, criteria_range 1 is year of students.
STEP 5:
After calculating criteria, next step is to calculate criteria 1.
According to the example, criteria 1 is 2nd year.
STEP 6:
Press Enter to get the results.
Returns 44, the minimum value among all the 2nd year students are 44.
