Minimum Value Based on Multiple Criteria in Excel (7 Ways)

Minimum Value Based on Multiple Criteria in Excel (7 Ways)

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.

Find Minimum Value Based on Multiple Criteria Using MIN and IF Functions

Step 1: Enter the formula in cell F15 and press Enter

=MIN(IF(C4:C12>7,IF(D4:D12>700,D4:D12)))

Formula Explanation

  • 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

Enter the formula in cell F15 and press Enter

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

=MINIFS(D4:D12,C4:C12,">7",D4:D12,">700")

Formula Explanation

  • 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

Formula using MINIFS function

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.

Determine Nth Smallest Value Using SMALL and IF Functions

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 

Formula using SMALL and IF functions

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

=SMALL(IF($C$4:$C$12>$C$15,IF($E$4:$E$12=$C$16,$F$4:$F$12)),B20)

Formula Explanation

  • 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

Copy down the formula

Step 3: Now enter the 7 as Quantity and Pending as Status

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.

SMALL IF Formula with Multiple OR Criteria

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)

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.

Enter the following formula in cell C20 and copy it down up to C22

[ 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.

Find N-th Smallest Value with OR Criteria Using SMALL and FILTER Functions

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)

Formula Explanation

  • 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.

Enter the following formula in cell C20 and copy it down up to C23

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.

Find Smallest Values Ignoring Zeros with Multiple Criteria

[ 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

=SMALL(IF($F$4:$F$12<>0,IF($E$4:$E$12=$C$15,$F$4:$F$12)),B19)

Formula Explanation

  • 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.

Enter the formula in cell C18 and copy it down up to C21

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

=AGGREGATE(15, 3,1/( ($C$4:$C$12>$C$15) * ($E$4:$E$12=$C$16)) * $F$4:$F$12, 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$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.

Formula with AGGREGATE function

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 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.

Conclusion

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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo