Method 1 – Find the 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. We will find the minimum food price where quantity is greater than 7 and the price is greater than $700.
Steps:
- Enter this 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
Method 2 – Find the Minimum Value Based on Multiple Criteria Using MINIFS function
- 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
Method 3 – Determine the Nth Smallest Value Using SMALL and IF Functions
Let’s find the three smallest prices where the quantity is greater than 7 and the status is Pending.
Steps:
- Enter the following formula in cell H8Â and press Enter (for Excel versions other than Excel 365, press Ctrl + Shift + Enter).
=SMALL(IF($C$5:$C$10>$H$4,IF($E$5:$E$10=$H$5,$D$5:$D$10)),G8)
- 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.
- 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
Method 4 – Use SMALL and IF Formula with Multiple OR Criteria
We’ll find out the top 3 lowest prices where Status is Pending or Processing.
Steps:
- Enter the following formula in cell G8Â and copy it down to G10. For versions other than Excel 365, use Ctrl + Shift + Enter to apply the formula.
=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
Method 5 – Find the N-th Smallest Value with OR Criteria Using SMALL and FILTER Functions
We will find the 4th smallest value where the status should is either Pending or Processing.
Steps:
- Enter the following formula in cell G8Â and copy it 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
Method 6 – Find Smallest Values Ignoring Zeroes with Multiple Criteria
Let’s find the lowest 3 prices where the price should not be equal to zero and the status is pending.
[ Note: Blank cells with only dash (-) signs are zero values cells since the format is Accounting.]
Steps:
- Enter the formula in cell G8 and copy it down to G10. Press Ctrl + Shift + Enter to apply it.
=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. IF($C$5:$C$10<>0Â is checking if the price is equal to zero or not. In Excel, the <> symbol is the NOT operation.
Read More: How to Use MIN Function to Exclude Zero in Excel
Method 7 – Find the Minimum Value Based on Multiple Criteria Using the AGGREGATE Function
- 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
Related Articles
<< Go Back to Excel MIN Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
