MINIFS Function

It returns the smallest numerical value based on the user-defined criteria for a range of values.

Author: Christopher Haynes
Christopher Haynes
Christopher Haynes
Asset Management | Investment Banking

Chris currently works as an investment associate with Ascension Ventures, a strategic healthcare venture fund that invests on behalf of thirteen of the nation's leading health systems with $88 billion in combined operating revenue. Previously, Chris served as an investment analyst with New Holland Capital, a hedge fund-of-funds asset management firm with $20 billion under management, and as an investment banking analyst in SunTrust Robinson Humphrey's Financial Sponsor Group.

Chris graduated Magna Cum Laude from the University of Florida with a Bachelor of Arts in Economics and earned a Master of Finance (MSF) from the Olin School of Business at Washington University in St. Louis.

Reviewed By: Kevin Henderson
Kevin Henderson
Kevin Henderson
Private Equity | Corporate Finance

Kevin is currently the Head of Execution and a Vice President at Ion Pacific, a merchant bank and asset manager based Hong Kong that invests in the technology sector globally. Prior to joining Ion Pacific, Kevin was a Vice President at Accordion Partners, a consulting firm that works with management teams at portfolio companies of leading private equity firms.

Previously, he was an Associate in the Power, Energy, and Infrastructure Investment Banking group at Lazard in New York where he completed numerous M&A transactions and advised corporate clients on a range of financial and strategic issues. Kevin began his career in corporate finance roles at Enbridge Inc. in Canada. During his time at Enbridge Kevin worked across the finance function gaining experience in treasury, corporate planning, and investor relations.

Kevin holds an MBA from Harvard Business School, a Bachelor of Commerce Degree from Queen's University and is a CFA Charterholder.

Last Updated:October 5, 2023

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.

Key Takeaways

  • You will get a #VALUE! error if the min_range and criteria_range are not of equal lengths
  • If the criteria that you input does not find a match in the criteria_range, Excel will return the result as zero.
  • You can use a combination of the MIN and IF/IFS functions for versions earlier than Excel 2019, since earlier versions don't support the use of the function and will return a #NAME? error.
  • For partial matches, you can use the logical operators along with the wildcard characters as a conditional expression for criteria
  • You can use up to 126 pairs of criteria and criteria_range argument in the MINIFS function

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

Note: You can input up to 126 arguments for the range/criteria combination in the MINIF function.

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:

Index

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:

Value

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.

Values

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:

Data

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:

Price

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:

Data

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.

Value

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:

Data

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:

Grade

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.

Result

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:

Data

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:

Loan

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.

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:

Value

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by James Fazeli-Sinaki | LinkedIn

Free Resources

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