How to Find Minimum Value Based on Multiple Criteria in Excel

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.

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]

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.