In this tutorial, I am going to show you 7 easy ways to find minimum value based on multiple criteria in Excel. 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 values based on multiple criteria in Excel.
How to Find Minimum Value Based on Multiple Criteria in Excel: 7 Ways
1. Find Minimum Value Based on Multiple Criteria Using MIN and IF Functions
Let’s assume we have a food order dataset with their product name, quantity, price, and status. Now we will find the minimum food price value based on multiple criteria where quantity is greater than 7 and price is greater than $700. For this, we will be using the MIN and IF functions in Excel.
Step 1: Enter the formula in cell D10Â and press Enter:
=MIN(IF(C5:C9>7,IF(D5:D9>700,D5:D9)))Formula Explanation
- Here IF(C5:C9>7) is our first condition and IF(D5:D9>700) is our second condition.
- After checking the conditions the MIN function will get the minimum price from D5:D9 this range.
Read More: How to Use Combined MIN and IF Function in Excel
2. Find Minimum Value Based on Multiple Criteria Using MINIFS function
Combining the MIN and IFS functions, we have another function in Excel named the MINIFS function. Here we will find the minimum value based on multiple criteria using the MINIFS function in excel for the above dataset.
Step 1: Enter the following formula in cell D10Â and press Enter:
=MINIFS(D5:D9,C5:C9,">7",D5:D9,">700")Formula Explanation
- In the MINIFS function, D5:D9 is the first minimum range.C5:C9 is the first criteria range, and “>7” is the first criterion.
- In the same way, D5:D9 is the second criterion range, and “>700” is the second criterion.
Read More: How to Find Minimum Value in Excel
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. We will use the SMALL function in excel here to find the 3 minimum value based on multiple criteria.
Step 1: Now Enter the following formula in cell H8Â 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.
=SMALL(IF($C$5:$C$10>$H$4,IF($E$5:$E$10=$H$5,$D$5:$D$10)),G8)
Step 2:Â Next copy this formula down using the Fill Handle.
Formula Explanation
- IF($C$5:$C$10>$H$4 is checking our first condition. We are checking if the quantity is greater than your entered number or not.
- IF($E$5:$E$10=$H$5,$D$5:$D$10)) this is the inner IF statement which is checking and returning the matched status rows.
- Lastly, G8Â this is defining the rank of the price.
[ Note: Make sure all Price cells are in proper currency format]
Read More: How to Find Lowest Value with Criteria in Excel
4. SMALL IF Formula with Multiple OR Criteria
Using the SMALL and 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. Let us see the steps to find the minimum value based on multiple criteria in Excel.
Step 1: Enter the following formula in cell G8Â and copy it down up to G10. 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(($D$5:$D$10=$F$5)+($D$5:$D$10=$G$5),$C$5:$C$10),F8)
Formula Explanation
- 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]
Read More: How to Find Minimum Value That Is Greater Than 0 in Excel
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 G8Â and copy it down up to G10.
=SMALL(FILTER($C$5:$C$10, ($D$5:$D$10=$F$5) + ($D$5:$D$10=$G$5)), F8)
Formula Explanation
- In the FILTER function, $C$%:$C$10 is the array range, ($D$5:$D$10=$F$5) + ($D$5:$D$10=$G$5) these are the two conditions that are acting like OR operation.
- Lastly, F8Â is defining the rank of the smallest value using the SMALL function.
Read More: How to Find Lowest 3 Values in Excel
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 any 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 G8 and copy it down to G10. We need to press CTRL + SHIFT + ENTER as this is an array formula.
=SMALL(IF($C$5:$C$10<>0,IF($D$5:$D$10=$F$5,$C$5:$C$10)),F8)
Formula Explanation
- This formula is like the previous one. Just in the first condition IF($C$5:$C$10<>0 this is checking if the price is equal to zero or not. In Excel, the <> symbol is the NOT operation.
Step 1: Finally from the picture, we can observe that this formula is ignoring the values which are zeros and counts the cells which are not zero and the status is pending.
Read More: How to Use MIN Function to Exclude Zero in Excel
7. Find Minimum Value Based on Multiple Criteria Using Aggregate Function
There is a powerful function in Excel which is the AGGREGATE function that can perform different aggregate operations like MAX, MIN, SUM, etc.
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 H8Â and press Enter:
=AGGREGATE(15, 3,1/( ($C$5:$C$10>$H$4) * ($E$5:$E$10=$H$5)) * $D$5:$D$10, 1)
Formula Explanation
- 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$5:$C$10>$H$4) * ($E$5:$E$10=$H$5)) 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.
Read More: How to Find Minimum Value with VLOOKUP in Excel
Things to Remember
| Common Errors | When they show |
|---|---|
| MAX Function | |
| 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. |
| MINIFS Function | |
| 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. |
| SMALL Function | |
| #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. |
| AGGREGATE Function | |
| #VALUE! |
If there is a second reference argument but not provided, AGGREGATE function returns a #VALUE! error. |
| #VALUE! | AGGREGATE returns the #VALUE! error value If there is one or multiple references are 3-D references |
| Horizontal Ranges | The AGGREGATE function is designed for vertical ranges, not horizontal ranges. |
Download the Practice Workbook
Conclusion
These are some ways to find minimum values 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.
Related Articles
<< Go Back to Excel MIN Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
