How to Find Minimum Value Based on Multiple Criteria in Excel
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]
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]
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.
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.
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.
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
Find Minimum Value.xlsx
Related Articles
- Excel MIN Function Returns 0
- Difference Between MAX and MIN Function in Excel
Save Saved Removed 0
Md. Abdullah Al Murad
Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find. Read Full Bio
