You’ll likely be familiar with the MIN function for taking the value of a range: MIN(A1:A5) will return the minimum value in cells A1:A5. If you need to take a conditional minimum, which applies to only some of the values in that range, without splitting your data up, then the Excel MINIFS function is what you’re looking for. This article is a complete guide to using the MINIFS function.
There are lots of possible uses, but some examples of when you might want to use the MINIFS function are:
- Find the minimum value which to a particular type in another column in your data. For example, in a list of account balances, you might want to find the minimum of a particular type of account, rather than the minimum of all.
- Find the minimum value based on the values themselves. For example, the minimum of all positive account balances.
It’s worth saying that, the Excel MINIFS function is only available in newer versions of Excel, such as Excel 2019 or Excel 365. If you are using an older version of Excel, a similar result is possible using an array formula, as we describe in the final section below: Conditional Min in Older Versions of Excel.
We’ve created some dummy data to show how it works in the examples below, which we’ll refer back to.
Function Fact Sheet: MINIFS
Excel Versions
This function is available in Excel 2019, Excel 365, or later.
Description
MINIFS lets you specify a data range plus criteria ranges and values. It will return the minimum value in the data range, where the criteria are met. This works in a similar way to functions such as SUMIFS.
For example, if your data is in range A1:A5, and your criteria are in B1:B5, then Excel will work out which values to take the min of by a process like the following:
- Does cell B1 meet the criteria? If so then include A1 in the calculation of the minimum, otherwise ignore it.
- Does cell B2 meet the criteria? If so then include A2 in the calculation of the minimum, otherwise ignore it.
- (and so on!)
So for MINIFS(A1:A5, B1:B5, "criteria"), only cells B1 and B3 meet the criteria, then only A1 and A3 will be taken account in the calculation of the minimum, and it the value returned will be the same as MIN(A1, A3).
Return Value
Returns the minimum value in the range, where the criteria are met.
Syntax
For one criterion: =MINIFS(min_range, criteria_range1, criteria1)
Two or more criteria: =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments
The MINIFS function is called with the following arguments:
min_range[Range]: The range of values you wish to find the minimum ofcriteria_range_1[Range]: The range of cells which are compared with criteria1 argument. In cases where this satisfies the criteria, the values in min_range will be included in the minimum calculation. This can be the same range as min_range.criteria1[Value]: The criteria used for comparing the values incriteria_range_1. This can be a value, (e.g. 1000), text (“Insurance”), can contain logical statements (“>1000”) and wildcards (“Ins*”).
Optionally, you can include additional pairs of criteria_range / criteria arguments to apply other conditions.
Examples
MINIFS with Single Criterion
In this section we look at some examples. One of the most straightforward uses is to calculate the minimum value in a range based on a single criterion. Using the example data above, we can calculate the minimum value (in range C2:C7) where the type (in B2:B7) is “Insurance”. We use absolute cell references by adding $ signs, so that the ranges wouldn’t be affected if the formula was copied elsewhere on the sheet, or filled down.
=MINIFS($C$2:$C$7,$B$2:$B$7,"Insurance")
As shown in the earlier screenshot, this would correctly return the value 500.
Alternatively, it’s possible to use the values themselves as a criteria. For example, suppose we want to find the minimum positive value – we can do by specifying the range containing the values in both the min_range and criteria_range arguments:
=MINIFS($C$2:$C$7,$C$2:$C$7,">0")
With our example data, this correctly excludes the negative values and returns 300.
MINIFS with Multiple Criteria
It’s also possible to combine multiple criteria and take the minimum where they both hold true – i.e. condition1 and condition2 are satisfied. Again using the example above, we can do this to find the minimum value where the value is positive and the contract type is “Savings”. For example, the following formula will return 300 based on our test data.
=MINIFS($C$2:$C$7,$C$2:$C$7,">0",$B$2:$B$7,"Savings")
As many additional criteria_range / criteria pairs as you need to the end of the formula.
The above will give the minimum if both criteria pass. To find the minimum if either criteria pass (i.e. condition1 or condition2), we need to take a slightly different approach and take the minimum of the separate results, for example:
=MIN(MINIFS($C$2:$C$7,$C$2:$C$7,">0"), MINIFS($C$2:$C$7,$B$2:$B$7,"Savings"))
Just to explain what this is doing, we can split it up as follows:
MINIFS($C$2:$C$7,$C$2:$C$7,">0")is the minimum of positive values (300)MINIFS($C$2:$C$7,$B$2:$B$7,"Savings")is the minimum of Savings account values (-50)- Putting these values in, the formula simplifies to
=MIN(300, -50), taking the lowest of these two values, which is -50.
MINIFS with Wildcards
As with formulas like SUMIFS, it is possible to use wildcards in the MINIFS formula. The wildcards available in Excel are:
*(asterisk) – match any number of other characters, e.g. the criteria “www.*.com” would match both “www.google.com” and “www.microsoft.com”, but not “example.com” (which doesn’t start with www).?(question mark) – match a single character, e.g. “Policy?” would match “Policy1” or “Policy9” but not “Policy10” (which was two extra characters.~(tilde) – used to match one of the wildcard characters in text, e.g. without this, the criteria “Why?” would match “Why?”, “Why1”, “WhyX”. If we just want to match the question “Why?”, we use the criteria “Why~?”
Some examples of MINIFS using these criteria are as follows (these don’t exactly correspond to the data above, but could be useful if the data was more complex than our simple example):
- To find the minimum where account number (in
A2:A7) starts with X=MINIFS($C$2:$C$7,$A$2:$A$7,"X*") - For the minimum where product type is, for example, “Insurance1”, “Insurance2”, etc (but not just “Insurance”):
=MINIFS($C$2:$C$7,$B$2:$B$7,"Insurance?") - And the minimum where product type is the exact text “Insurance?” (e.g. who ever was entering data has guessed about the account type):
=MINIFS($C$2:$C$7,$B$2:$B$7,"Insurance?")
MINIFS with Not Equals Criteria
In the above examples, we’ve been testing for matches to the criteria we gave. It’s also possible to turn it round and find the minimum where the criteria_range is not equal to some value. As an example, we can rewrite our earlier example to find the minimum Insurance contract value by specifying that it is not a Savings contract instead. Do to this we use the <> not equal to operator:
=MINIFS($C$2:$C$7,$B$2:$B$7,"<>Savings")
This returns the minimum of all values where the account type is not “Savings”. In our simple example with only two account types this gives the value 500, which was the same as in the earlier example looking for the minimum “Insurance” account value. However, in real data with several account types, we could use this to find the minimum excluding a particular type.
MINIFS where cells are not blank
Another usage might be to find the minimum value where the corresponding cells in a different range are not blank (i.e. ignore the blank cells). To do this, you can simply enter the criteria_value as “<>”, for example, to exclude blank entries in our example above (there aren’t any in the data shown though), the formula would be:
=MINIFS($C$2:$C$7,$B$2:$B$7,”<>”)
Another case might be where the values themselves contain some blank cells. In this case, the MIN() function would actually ignore the blank cells and return the minimum of the other values, but you can achieve the same result with MINIFS as follows by entering the same range for both value_range and criteria_range, and then specifying not blank:
=MINIFS($C$2:$C$7,$C$2:$C$7,”<>”)
MINIFS using a value from another cell
Typing in the criteria each time will get tiring! And will be impossible if you have a large range of values, or if you don’t know what these might be when building the spreadsheet. You can automate this by using the value from a different cell in the criteria range – just type the address of the cell containing the criteria in that argument, e.g. “$B10”:
=MINIFS($C$2:$C$7,$B$2:$B$7,$B10)
You can use this to build up tables of minima by filling down, as shown in the example below.
As show above, this also lets you put conditional statements (e.g. “>0”) and wildcards (“Ins*”) in those cells, and works just as if you had entered them directly in the formula.
Conditional MIN in Older Versions of Excel
It’s great if you have Excel 2019 or later and can use the MINIFS function. However, in some cases you won’t have this version. Particularly in a corporate environment where there is often a lag updating to the latest software versions. As I write this in 2022, I have one work environment where I need to use Excel 2010! This means that spreadsheets developed in the more recent version have to be backwards compatible so can’t take advantage of new formulas.
If that’s the case, then you can achieve a similar result to MINIFS using MIN with array formula. Using the example data above, we can re-write this as an array formula as follows. Remember when entering an array formula to press SHIFT + ENTER after typing the formula in, otherwise it may not behave as expected.
To find the minimum insurance account number (i.e. minimum of C2:C7 where B2:B7 equals “Insurance”):
- MINIFS version:
=MINIFS($C$2:$C$7,$B$2:$B$7,"Insurance") - Array formula:
=MIN(IF($B$2:$B$7="Insurance",$C$2:$C$7,""))
To find the minimum positive account balance (i.e. minimum of C2:C7 where those values are greater than zero):
- MINIFS version:
=MINIFS($C$2:$C$7,$C$2:$C$7,">0") - Array formula:
=MIN(IF($C$2:$C$7>0,$C$2:$C$7,""))
In these cases, we are replacing the values which do not meet our criteria with an empty string value (“”), which is then ignored by MIN(), so that only those values which meet our criteria are included.
It’s also possible to specify multiple criteria, but unfortunately the AND function doesn’t work as you might think in array formulas (it only returns a single value rather than an array), so we have to build this up as a series of nested IF functions.
With our example, to find the minimum positive account balance of insurance contracts, we could do:
- MINIFS version:
=MINIFS($C$2:$C$7,$C$2:$C$7,">0",$B$2:$B$7,"Insurance") - Array formula:
=MIN(IF($C$2:$C$7>0,IF($B$2:$B$7="Insurance",$C$2:$C$7,""),""))