MINIFS Function

What is the MINIFS Function?

The MINIFS function returns the smallest number based on one or more criteria set for a range of values. The process supports the use of logical operators such as more significant than (>), lesser than (<), equal to (=), lesser than or equal to (<=), greater than or equal to (>=), and not comparable to (<>).

The function can be used in the 2019 version of Excel and Excel 365. However, if you use a prior version, i.e., Excel 2016 and below, you can still use a combination of functions to give you similar results. This is covered in the section at the end.

In this article, you will understand what the function does, its syntax, and how to use it while working as a financial analyst.

If you want to read more about the comparison operators, we have covered a section in our IF function article.

Understanding the MINIFS function

The function is categorized as a Statistical function. It returns the smallest numerical value based on the user-defined criteria for a range of values.

Its traditional form is a combination of the MIN and IF/IFS functions, categorized as Statistical and Logical, respectively.

The MIN function finds the minimum value based on a referenced range of cells, while the IF/IFS function returns either TRUE or FALSE based on whether the condition(s) is/are fulfilled.

For example, suppose you have three numbers, 10, 20, and 30. The criteria you input for those numbers is "X must be greater than or equal to 20." The two numbers that fulfill the criteria are 20 and 30.

Next, with those same numbers, the MIN function returns the minimum value, which, in this case, is equal to 20.

Thus, instead of using two different functions (we have covered a section on this later in the article), Excel came up with this function that fulfills a similar purpose.

MINIFS function Formula

Many Excel users in the community always complain that they have trouble understanding a function's syntax or how it works.

However, the function falls in neither category. With just three required arguments, it's one of the most accessible functions to understand and tremendously improves the user's efficiency.

The syntax for the function is:

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria_2]...)

where,

• min_range = (required): The range from which you need to determine the minimum numerical value
• criteria_range1 = (required): The first referenced range that will be evaluated for the criteria
• criteria1 = (required): The first condition in the form of a logical expression or statement according to which the minimum value will be returned
• criteria_range2 = (optional): The second referenced range that will be evaluated for the criteria
• criteria_2 = (optional): The second condition in the form of a logical expression or statement according to which the minimum value will be returned

How to use the MINIFS Function in Excel?

The function itself is pretty easy to use. All you need to do is select the range from which you need to determine the minimum value, reference the content for criteria evaluation, and, finally, state the conditional expression.

You can set up either one or multiple conditions depending on your dataset.

1. Minimum value based on one criterion

Suppose that, as a day trader, you made several trades on various S&P 500 ($SPX) and Nasdaq 100 ($NDX) index options. In addition, since the market was trending upward, you purchased several call options. The trade log is illustrated below:

If you need to determine the lowest price that you paid for an NDX 13400 CE option, you can use the formula.

=MINIFS(D3:D12,C3:C12,G4)

To give you the following result:

The lowest-priced NDX 13400 CE option was bought at $380.00, which you can confirm was trade number 7 in the table. Think about the MINIFS function this way - based on the criteria, you are using the 'filter' function, and then the minimum value from the qualifying range of cells is returned.

Similarly, by changing the value in cell D14 to "SPX 4150 CE", you will automatically get the minimum price paid for the option as $128.00.

2. Minimum value based on two criteria

Assume that you are looking to buy a house in either New York, Chicago, or Los Angeles. First, you go to your real estate agent, who, based on your preferences, provides you with a list of potential houses that you could buy. The data is as illustrated below:

The price for the same house type in a similar city differs based on the property's location. For example, you buy a 'Single Family Home' in New York. However, due to budget constraints, you must pick the house with the lowest price.

We will use the formula to get the lowest-priced 'single-family home' in New York.

=MINIFS(E3:E16,C3:C16,"Single Family Home",D3:D16,"New York")

Which will give you the following result:

The cheapest single-family home in New York costs $229,329. You can also check the other most affordable options based on different conditions and determine what property works best for you.

3. Criteria using logical operators

So far, we have only seen how you can set up a criterion using a text and find the minimum value from a range of cells. However, the function is not limited to only using text as criteria.

You can also use the logical operators, such as greater than (>), lesser than (<), equal to (=), greater than or equal to (>=), lesser than or equal to (<=), and not equal to (<>).

Let's assume you invested in some stocks, giving you good profits. Then, the P&L statement for the various trades is illustrated below:

Let's say you want to find the minimum profit greater than $1,000 you have earned on stocks other than Tesla. For this, you can use the formula.

=MINIFS(D3:D16,C3:C16,"<>Tesla",D3:D16,">1000")

This will give you the result a$1,232 for Netflix Inc.

Here, we use the not equal to (<>) operator that returns TRUE for all values except 'Tesla.' Once it forms a list of all the values in the range except Tesla, it finds the minimum value greater than 1000, which, in our case, is the profit of $1,232 for Netflix Inc.

Alternatives to MINIFS Functions

If you are using an Excel version before 2019, chances are you cannot use the function. This is because whenever you use the part, Excel throws back a #NAME? Error at you. This usually occurs when you have misspelled the function or, in this case, if the function does not exist in the Excel version.

Alternatively, you can use a combination of the MIN and IF (or IFS) functions to return the minimum value based on specific criteria.

Suppose the teacher in the classroom needs to find out the lowest English score. The dataset in the spreadsheet is illustrated below:

If you want to find the lowest mark scored by a student who has an A grade on the English exam, you can use the formula.

=MIN(IF(D3:D12="A",E3:E12))

To give you the following result:

The lowest 'A' grade score in English is 73, which Raven Fowler scores. Similarly, you can also use the function using the formula

=MINIFS(E3:E12,D3:D12,"A")

which will give you the same result: 73.

Practical Example of MINIFS Function

One of the practical applications of the function is finding the minimum value that is greater than zero.

Suppose that you work at a bank and need to determine what candidates can pay off their mortgage in a lump sum amount to improve the cash inflows at the bank. So you prepare the list of potential borrowers shown below:

In the first screening of candidates, you determine that the minimum loan balance must be greater than $0 but less than $35,000. To find the minimum loan value, you will use the formula.

=MINIFS(D3:D12,D3:D12, ">0",D3:D12,"<35000")

Which will give you the following result:

So, should Jarrett Crosby agree to pay $30,988, he can settle his mortgage. You contact him, but he says he might be unable to make the lump sum payment and intends to pay off the debt through monthly payments.

To find the next person, you don't need to change the formula; cross out the name from Excel. If the result returns zero, Excel cannot find a result matching our criteria.

In this case, we can change the criteria for the loan amount to be between $35,000 and $50,000, making our formula become.

=MINIFS(D3:D11,D3:D11, ">35000",D3:D11,"<50000")

To give the following result:

Conclusion

The MINIFS function in Excel offers a powerful tool for financial analysts and data professionals to efficiently extract specific data based on multiple criteria. Its straightforward syntax and flexibility allow users to easily determine the minimum value from a range of cells that meet certain conditions.

Moreover, the practical examples provided demonstrate the real-world applications of the MINIFS function, from analyzing stock profits to screening potential borrowers for loans. 

This function streamlines decision-making processes by swiftly pinpointing relevant data points, thereby enhancing productivity and accuracy in financial modeling and analysis tasks.

The MINIFS function stands as a valuable asset in Excel's arsenal of analytical tools, offering a user-friendly solution for data manipulation and analysis.

Whether used by financial professionals, educators, or researchers, the MINIFS function empowers users to make informed decisions based on precise data-driven evaluations.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• PEARSON Function
• PI Function
• SEARCH Function
• SQRT Function
• SUBTOTAL Function