MAXIFS function

The MAXIFS function in Excel returns the maximum value in a range based on one or more criteria. It allows you to find the highest number in a dataset that meets specific conditions. This function is part of Excel’s conditional functions and is useful when you need to apply multiple criteria to find the maximum value.

Syntax:

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments:

• max_range: Required. The range of cells containing the values from which you want to find the maximum.
• 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 that you want to apply. You can include up to 127 pairs of criteria_range and criteria.

Output:

The MAXIFS function returns the maximum value from the max_range that satisfies all of the specified conditions. If no data meets the criteria, the function returns a #VALUE! error.

How It Works:

MAXIFS works by evaluating each cell in the max_range and checking whether it satisfies the conditions in the corresponding criteria_range. If the value meets all the conditions, it is included in the calculation. The function then returns the highest value that meets all criteria.

Example:

1. Example 1: Finding the Maximum 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 maximum 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 maximum sales value in the “North” region, use the formula:

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

   This will return 200, which is the highest 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 maximum sales value in the “North” region where the status is “Yes”, use the formula:

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

   This will return 200, because it is the highest 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 maximum sales value greater than 120 in the “North” region, use the formula:

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

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

Key Points:

• MAXIFS allows you to apply multiple conditions (criteria) to a range, and it returns the maximum value that meets those conditions.
• Unlike the MAX function, which only returns the maximum value of a range, MAXIFS allows you to filter the data based on conditions.
• If no values meet the criteria, MAXIFS returns a #VALUE! error.
• You can use comparison operators (e.g., >, <, >=, <=, =, <>) as criteria in MAXIFS.

Use Cases:

• Sales data analysis: Find the maximum sales value for specific regions, time periods, or product categories.
• Performance tracking: Determine the highest performance score for students, employees, or teams under specific conditions.
• Financial analysis: Find the maximum return on investments or prices for a certain period or market condition.

Notes:

• MAXIFS was introduced in Excel 2016. If you’re using an older version of Excel, this function won’t be available.
• If no values meet the criteria, MAXIFS will return a #VALUE! error, so ensure that the criteria are set properly.