MS Excel provides a simple function called MIN which returns a minimum value from any range. But there might be a case where we need to find or calculate a minimum value based on one or multiple criteria. There are a lot of ways to calculate this type of calculation in Excel. In this article, I will show multiple ways to determine or find minimum value based on multiple criteria in Excel.
Download the Practice Workbook
7 Ways to Find Minimum Value Based on Multiple Criteria
1. Find Minimum Value Based on Multiple Criteria Using MIN and IF Functions
Let’s assume we have a food order dataset with their names, quantity, price, delivery date, and status. Now we will find out the minimum food price where quantity is greater than 7 and price is greater than $700.
Step 1: Enter the formula in cell F15 and press Enter
- Here IF(C4:C12>7 is our first condition and IF(D4:D12>700 is our second condition.
- After checking the conditions MIN function will get the minimum price from D4:D12 this range. If you want to explore more about this MIN function you can check this link
2. Find Minimum Value Based on Multiple Criteria Using MINIFS function
Combining the MIN and IFS functions, we have another function in Excel named MINIFS. Here we will find the minimum value based on multiple criteria using the MINIFS function for the above dataset.
Step 1: Enter the following formula in cell F15 and press Enter
- In the MINIFS function D4:D12 is the first minimum range.C4:C12 is the first criteria range and “>7” is the first criteria.
- In the same way, D4:D12 is the second criteria range, and “>700” is the second criteria.
- If you want to learn more about the MINIFS function you can check this link
3. Determine Nth Smallest Value Using SMALL and IF Functions
Now in this section, we will see how we can find out the N-th smallest value from any dataset. Our concern is to find the three smallest prices where the quantity is greater than 7 and the status is Pending.
Step 1: Now Enter the following formula in cell C20 and press Enter. In Excel 365 it will work properly but for the other version you need to put it like an array formula, so you have to press CTRL + SHIFT + Enter
Step 2: Now copy the formula up to C22. There will be #NUM! error as there is no input value in the cell. This will disappear when you enter the input
- IF($C$4:$C$12>$C$15 is checking our first condition. We are checking if the quantity is greater than your entered number or not. To know more about the IF function, you can check this link
- IF($E$4:$E$12=$C$16,$F$4:$F$12)) this is the inner IF statement which is checking and returning the matched status rows. Then from $F$4:$F$12 this range we will get the matched row price.
- Lastly, B20 this is defining the rank of the price. If you want to explore more about this SMALL function you can check this link
Step 3: Now enter the 7 as Quantity and Pending as Status
[ Note: Make sure all Price cells are in proper currency format]
4. SMALL IF Formula with Multiple OR Criteria
Using the SMALL IF functions formula we can do multiple or operations in Excel. Here our target is to find out the top 3 lowest prices where Status is Pending OR Processing.
Step 1: Enter the following formula in cell C20 and copy it down up to C22. And again, In Excel 365 it will work properly but for the other version you need to put it like an array formula, so you must press CTRL + SHIFT + Enter
=SMALL(IF(($E$4:$E$12=$B$16) + ($E$4:$E$12=$C$16), $F$4:$F$12), B20)
- This formula is working like the previous one. But here between the two conditions, we are using the plus (+) operator to make this an OR operation.
[ Note: Make sure all Price cells are in proper currency format]
5. Find N-th Smallest Value with OR Criteria Using SMALL and FILTER Functions
Up to now, we have seen how to find N-th smallest value and how to find the smallest values with OR operation. Now here we will see the way of finding the 4th smallest value with OR criteria using SMALL and FILTER functions. And our OR condition is the status should be either Pending or Processing.
Step 1: Enter the following formula in cell C20 and copy it down up to C23
=SMALL(FILTER($F$4:$F$12, ($E$4:$E$12=$B$16) + ($E$4:$E$12=$C$16)), $B20)
- In the FILTER function, $F$4:$F$12 is the array range, $E$4:$E$12=$B$16) + ($E$4:$E$12=$C$16)) these are the two conditions that are acting like OR operation. If you want to explore more about this FILTER function, you can check this link
- Lastly, $B20 is defining the rank of the smallest value using the SMALL function.
6. Find Smallest Values Ignoring Zeros with Multiple Criteria
At the time of calculating minimum values, sometimes we need to ignore zeros. Up to now all the processes that we have used will count zero at the time of determining minimum values. Now we will see how to find the smallest values ignoring zeros in Excel. First, we will see SMALL and IF functions for not zero then we will see the same formula for greater than zero with the condition.
Here our task is to find out the lowest 3 prices where the price should not be equal to zero and the status is pending.
[ Note: Here the blank cells with only dash (-) signs are zero values cells. In Excel if the format is Accounting, you cannot directly enter any zero values on that cell.]
Step 1: Enter the formula in cell C18 and copy it down up to C21. We need to press CTRL + SHIFT + ENTER as this is an array formula
- This formula is like the previous one. Just in the first condition IF($F$4:$F$12<>0 this is checking if the price is equal to zero or not. In Excel, the <> symbol is the NOT operation.
Now from the picture, we can observe that this formula is ignoring the values which are zeros and counting the cells which are not zero and the status is pending.
7. Find Minimum Value Based on Multiple Criteria Using Aggregate Function
There is a powerful function in Excel named AGGREGATE which can perform different aggregate operations like MAX, MIN, SUM, etc. Details of this function can be found in this link
We can use this function to act like the MINIFS function to find the minimum values with conditions. Again, we will be using the same dataset.
Step 1: Enter the formula in cell C20 and copy it down up to C22
- In the function 15 is used as are calculating the smallest number, then 3 is used as we are ignoring hidden rows, error values, nested subtotal, and aggregate functions.
- 1/( ($C$4:$C$12>$C$15) * ($E$4:$E$12=$C$16)) this part will return 0 or 1, like True or False. If both conditions are met, then it will return 1 otherwise 0.
- This 1 or True will be multiplied with the price columns and return the selected price.
- Lastly, 1 is defining the first lowest value from the selected rows.
Things to Remember
|Common Errors||When they show|
|MAX returns 0||The MAX function in current versions of Excel accepts up to 255 arguments. If arguments contain no numbers, MAX returns 0.|
|Ignores Empty Cells.||The MAX function ignores empty cells.|
|Return zero (0)||MINIFS will return zero (0) if no cells match the criteria.|
|#SPILL!||This particular #SPILL! The error usually means that your formula is relying on the implicit intersection for the lookup value and using an entire column as a reference.|
|#VALUE in MINIFS||MINIFS will return a #VALUE error if a criteria range is not the same size as the min_range.|
|#VALUE!||This kind of error occurs when the supplied n is a non-numeric value.|
|#NUM!||This kind of error occurs when the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array.|
||If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error.|
|#VALUE!||If there is one or more of the references are 3-D references, AGGREGATE returns the #VALUE! error value.|
|Horizontal Ranges||The AGGREGATE function is designed for vertical ranges, not horizontal ranges.|
These are some ways to find minimum value based on multiple criteria in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.