How to use the MIN Function With Multiple IF Functions
If you’re using an older version of Excel that doesn’t support MINIFS, you can use an array formula with MIN and IF functions to achieve the same result.
MIN function in combination with multiple IF functions to find the minimum value based on specific conditions. This approach can be particularly useful when you have complex criteria that you want to apply to your data. If we want to get the smallest value from a range based on singe or multiple conditions, in that case we can make use of MIN with IF function. There can be multiple number of IF based on how many conditions you want to apply.
MIN with IF is an array function, so to execute the cell we should run the formula cell with CTRL + SHIFT + ENTER. You can add as many conditions as needed to meet your specific criteria.
Here’s a general formula using the MIN function with multiple IF functions:
=MIN(IF(condition1, range), IF(condition2, range), IF(condition3, range), …)
- condition1, condition2, condition3…, : These are the conditions you want to apply to your data. Each condition should return either TRUE or FALSE.
- range: This is the range of values you want to evaluate. The IF functions will return values from this range if the corresponding condition is TRUE.
Suppose we need to find the minimum number of “Cupcakes” sold in “Banglore” city.
Here, our first criteria range will be A2:A14 and the value we want from this range is F4(Cupcakes), next criteria range is C2:C14 and the value we want from this range is G4(Banglore). And the final output we need after checking both the criteria if the minimum quantity sold from range D2:D14.
Note: Remember to enter this formula as an array formula by pressing Ctrl+Shift+Enter to ensure it works correctly.
Alternatively, if you want to write the above formula skipping multiple IF conditions, you can make use of (*) symbol. It will give the same result.
How to use MINIFS function in Microsoft Excel
The MINIFS function in Excel is a powerful tool for filtering and analyzing data. It allows you to find the smallest numeric value in a range of cells that meet one or more specified requirements. This can be useful for a variety of tasks, such as finding the lowest price for a product in a specific category, finding the shortest delivery time for a product, finding the highest test score for a student in a particular class, or finding the lowest employee salary in a department.
Contact Us