How To Get Minimum Value If Multiple Criteria In Excel
If you have data you want to get the minimum value if multiple criteria use an array of formula based on the MIN and IF functions. Here is how the formula looks:
Generic Formula:
=MIN(IF(range1=criteria1,IF(range2=criteria2,values)))
…..where the “range1 and range2” are the sizes of the criteria and “values” is the quantity figure you are looking for.
Example Problem and Solution
Assume that you are operating a boutique dealing with a vast range of clothes and apparels. If you want to get the minimum value or minimum price for each item in the stock, using multiple criteria, this is how you can apply the formula. Check out this example:
Figure 1. Example 1 of MIN/IF Function
From this example, here is the formula in H7:
=MIN(IF(C5:C16=G7,IF(B5:B16=F7,D5:D16)))
……where B5:B16 is the item range, C5:C16 is is color range and D5:D16 is the price range.
The solution to this problem as follows;
- With a hue of “Blue”, the item of “T-shirt”, the answer is $1.3. That is the minimum price. However, for this formula to work, enter it manually using Ctrl+Shift+entered
How the MIN/IF Formula Works
If you look at the formula, you will notice that there are two IF functions inside MIN. The reason for these two functions is to return minimum prices using the two criteria. For the first IF function, C5:C16=G7, check color against what’s in G7, “Blue”. The resulting array looks like this:
{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}
For the 2nd IF statement, B5:B16=F7, items are checked against what value is in cell F7, “T-shirt”, and the resulting array looks like this:
{TRUE;TRUE;TRUE;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;TRUE; FALSE;TRUE)
The “value” for the second IF function also takes into account the price range (D5:D16), which give an array of price from D5…..to D16. Here is the array:
{1.30;3.32;1.4;1.4;1.23;1.31;1.41;1.44;1.32;1.23;5.33;4.32}
Each item’s price in the range is returned but only if the result of the two IF is TRUE for values or in the case the items that correspond in positions. In that cases, the final array netted in the MIN function will look like this:
=MIN(1.30;3.32;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;4.32)
….The MIN function will then return the lowest price for “Red T-shirts” and ignore FALSE values. Therefore the solution is $1.3.
That’s how you get the minimum value using multiple criteria. It is a straightforward formula in the application.
Leave a Comment