MINIFS function

The MINIFS function in Excel returns the minimum value in a range based on one or more specified criteria. It is part of Excel’s conditional functions, allowing you to find the smallest value in a dataset that meets certain conditions or criteria.

Syntax:

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments:

• min_range: Required. The range of cells containing the values from which you want to find the minimum.
• criteria_range1: Required. The range of cells that you want to apply the first condition (criteria) to.
• criteria1: Required. The condition (criteria) to apply to criteria_range1. This can be a number, expression, cell reference, or text.
• criteria_range2, criteria2, …: Optional. Additional ranges and criteria to apply. You can include up to 127 pairs of criteria_range and criteria.

Output:

The MINIFS function returns the minimum value from the min_range that satisfies all the specified conditions (criteria). If no data meets the criteria, the function returns a #VALUE! error.

How It Works:

MINIFS works by evaluating each cell in the min_range and checking whether it satisfies the conditions in the corresponding criteria_range. If a value meets all of the specified conditions, it is included in the calculation. The function then returns the smallest value from those that meet the criteria.

Example:

1. Example 1: Finding the Minimum Value Based on One Condition Suppose you have a list of sales data in columns A (Sales) and B (Region). You want to find the minimum sales value in a specific region.

   A1: 100 | B1: North
   A2: 150 | B2: South
   A3: 200 | B3: North
   A4: 120 | B4: South
   

   To find the minimum sales value in the “North” region, use the formula:

   =MINIFS(A1:A4, B1:B4, "North")
   

   This will return 100, as it is the lowest sales value in the “North” region.

2. Example 2: Using Multiple Conditions Suppose you have the following data:

   A1: 100 | B1: North | C1: Yes
   A2: 150 | B2: South | C2: No
   A3: 200 | B3: North | C3: Yes
   A4: 120 | B4: South | C4: Yes
   

   To find the minimum sales value in the “North” region where the status is “Yes”, use the formula:

   =MINIFS(A1:A4, B1:B4, "North", C1:C4, "Yes")
   

   This will return 100, because it is the lowest sales value in the “North” region where the status is “Yes”.

3. Example 3: Using Criteria with Comparison Operators Suppose you have the following data:

   A1: 100 | B1: North
   A2: 150 | B2: South
   A3: 200 | B3: North
   A4: 120 | B4: South
   

   To find the minimum sales value greater than 120 in the “North” region, use the formula:

   =MINIFS(A1:A4, B1:B4, "North", A1:A4, ">120")
   

   This will return 200, as it is the smallest sales value in the “North” region that is greater than 120.

Key Points:

• MINIFS allows you to apply multiple conditions (criteria) to find the minimum value in a range.
• Unlike the MIN function, which only returns the smallest value of a range, MINIFS lets you filter the data based on specific conditions.
• If no data meets the criteria, MINIFS will return a #VALUE! error.
• You can use comparison operators (e.g., >, <, >=, <=, =, <>) as criteria in MINIFS.

Use Cases:

• Sales analysis: Find the lowest sales value for specific products, time periods, or regions.
• Performance tracking: Identify the lowest performance score for students, employees, or teams under specific conditions.
• Financial analysis: Find the minimum price, cost, or return on investment for a certain period or market condition.

Notes:

• MINIFS was introduced in Excel 2016. If you’re using an older version of Excel, this function won’t be available.
• If there are any blank cells in the criteria range, they are ignored.
• Be cautious of empty criteria ranges as they may lead to unexpected results.