How to use MINIFS to get minimum value based on multiple criteria
The above example depicted the use of MINIFS when there is only one criterion available. What if we need to fetch minimum value from a range based on multiple criteria. You can use it when you have a dataset with multiple conditions that need to be met before calculating the minimum value.
Here’s the syntax of using MINIFS with multiple criteria:
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- min_range: This is the range of values you want to find the minimum from.
- criteria_range1: The first range where you want to apply a condition.
- criteria1: The condition to apply to criteria_range1.
- [criteria_range2, criteria2, …]: Optional additional ranges and criteria. You can have multiple pairs of criteria ranges and criteria.
Lets’s say we need to fetch the minimum value of quantity sold of “Black Coffee” in “Chennai” city. The formula for this condition can be written as :
=MINIFS(D2:D14,A2:A14,F4,C2:C14,G4)
- D2:D14 is the min_range, where we want to find the minimum quantity sold.
- A2:A14 is the first criteria range, which checks the “Product” column for “Black Coffee”.
- F4 is the first criteria i.e., Black Coffee.
- C2:C14 is the second criteria range, which checks the “City” column for “Chennai”.
- G4 is the second criteria i.e., Chennai.
The MINIFS function will return the minimum quantity sold that meets both criteria, which in this example would be 450.
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