In our previous blogs, we learned about how to use the MAXIFS function in Excel to find the maximum value with a specified set of conditions. The MINIFS formula in excel works just opposite to the MAXIFS function.
In this tutorial, we would learn everything about using the excel MINIFS function, when to use, how to use, syntax, arguments, and examples.
So, let us begin 😎
When to Use MINIFS Formula in Excel
The MINIFS function in Excel helps to find the minimum value when the specified set of criteria fulfils. You can specify multiple criterias in the MINIFS function
This is one of the useful inbuilt statistical functions in Excel.
To find the minimum monthly sales of a company MIN formula of Excel comes into its use. But, to find the minimum sales of one of its branches, the MINIFS function of excel becomes the solution.
Syntax and Arguments
=MINIFS(min_range,criteria_range1,criteria1….)
- min_range– In this argument, specify the range of cell to look for the minimum value.
- criteria_range1 – In this argument, specify the range of cell in which you want to check for the criteria.
- criteria1 – In this argument, specify the criteria. This can be an expression containing text string and/or logical operators.
You can define 126 pairs of criteria ranges and their corresponding criterias. The MINIFS function would give you a resultant value that satisfies all of them.
How Does MINIFS Function Works In Excel
Firstly, an array of values that meets the specified condition in MINIFS is created ( you will not be able to see it). The Function returns the minimum value from that array of elements.
We can define multiple conditions in one MINIFS formula. In that case, the function will use the AND logical operator concept. For instance, it would return the minimum value that meets all of the conditions.
Must To Know Points About MINIFS Formula of Excel
You must keep the following points in mind before using the MINIFS function of excel:
- The function ignores the text strings, logical values (TRUE and FALSE).
- The size (i.e. number of cells) of min_range and criteria_range should be same.
- The function criteria can be an expression with logical operators like >, <, =, <>.
- Use Wild Cards (* or ?) when you have a rough (fuzzy) guess for the matching ctiteria in the criteria range. Following are the wild cards in excel.
- ? – used for one character.
- * – used for a sequence of characters.
- For the function criteria expression, keep the following points in mind:-
- Dates and text strings are supposed to be put within double quotes, whereas numbers and cell resfrences must not be put within double-quotes.
- Whenever we use a logical operator in the criteria, the entire expression is to be enclosed within double quotes like “<30”. On the other hand, formula and cell refrence must be concatenated using an ampersand (&) like this “>”&A1 or “<“&TODAY().
Examples to Use MINIFS Function In Excel
In this section, let us learn about how MINIFS formula works with the help of some examples.
Ex. 1 – Passing One Condition to MINIFS Function | Basic Example
The below example contains the time taken by different students in inter-house lemon spoon race organized in a school.
Suppose, you want to find who is the first position holder from ‘Rose’ group (i.e. one who took minimum time to complete the race).
=MINIFS(A2:A8,B2:B8,"Rose")
As a result, excel formula returns 10.3 as function output.
Explanation – In this formula, we have passed A2:A8 as the min range (containing the function result). The range B2:B8 is the criteria range, on which we have applied our criteria to “rose”. Similarly, all the records of the Rose group are highlighted. The function has returned the corresponding minimum time taken by the student of rose house.
After that, we will be passing multiple conditions to Excel MINIFS function.
Ex. 2 – Passing Multiple Conditions to MINIFS Function | Example
Now let us take an example where we would be using multiple criteria in MINIFS function of Excel.
The below image shows the details of customer bank accounts. Column A shows whether the account is active or not. Likewise, column B shows if KYC details are updated or not and column C contains the closing account balances.
The question here comes how would you know the minimum balance of a customer’s account whose KYC is not updated AND has an inactive account status.
MINIFS function can easily do this for you 😉
=MINIFS(C2:C8,A2:A8,"Inactive",B2:B8,"No")
As a result, the function returns 200, as the minimum account balance.
Explanation – Firstly, we know that there were two conditions applicable over here. The first condition is on range A2:A8 (viz. inactive) and the second condition is on range B2:B8 (viz. No). The function looks for the cells that match both criteria simultaneously. Highlighted records are those satisfying both of the conditions. Finally, the formula finds the minimum value from the cells which satisfies both the conditions simultaneously (i.e. from cells C3 and C6).
Ex. 3 – Passing Another Formula to MINIFS Function Criteria
Now that we have learned some basic examples of MINIFS function, let us deep dive into this function a bit more.
Let us assume that there is an order dispatching company with dispatch date and quantity to be dispatched.
The coordinator decides to start his work by dispatching the minimum quantity order whose dispatching date has crossed today.
To achieve this, use the following Excel MINIFS formula.
=MINIFS(B2:B10,A2:A10,"<"&TODAY())
As a result, the function has returned 13.
Explanation – TODAY() function in excel returns the current date. The criteria is set to “<“&TODAY(). It means that the condition applies on dates that are before current date. All the records whose despatching dates have passed are in gray color (above image). The range B2:B10 set as min_range will return the minimum value of a quantity that satisfies the condition (viz. before today’s date). Consequently, 13 is the lowest product quantity to start the day with.
Ex. 4 – Using (<>) Not Equal Logical Operator in Excel MINIFS Function
The logical operator (<>) not equal helps to exclude a set of elements from the array.
For instance, let us suppose there is a pet shop. The dogs are fed on special meals and remaining animals like cats, parrots, calves, and pigs are served with normal meals. The owner wants to know the minimum consumption of normal meals.
Enter the below-mentioned formula to get the results.
=MINIFS(C2:C6,B2:B6,"<>Special")
As a result, the MINIFS formula in excel has returned 20.
Explanation – The min_range in the formula is C2:C6 where we are looking for the minimum value. The range B2:B6 is the criteria_range where we have applied the condition “<>Special”. The condition has a logical operator not equals to. Therefore, the highlighted cells are the records that meet this criterion. Based on this, the function returns the corresponding minimum value from the min_range i.e. 20.
With this we are through with this content. It’s time for you to practice now 😉
Thank you for reading. 🙂