The MAXIFS function in Google Sheets is a powerful tool that allows users to determine the maximum value from a range of cells while applying one or more criteria for filtering. This function is particularly useful when analyzing large datasets where only specific values are needed based on certain conditions.

Syntax

MAXIFS(max_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

• max_range: The range of cells from which you want to find the maximum value.
• criteria_range1: The first range of cells that you want to evaluate against criterion1.
• criterion1: The condition that must be met in criteria_range1 for the corresponding value in max_range to be considered.
• [criteria_range2, criterion2, …]: Optional additional ranges and criteria pairs that further filter the max_range.

Example #1

MAXIFS(A2:A10, B2:B10, "Red")

This function finds the highest value in the range A2:A10 where the corresponding value in B2:B10 equals “Red.” If A2:A10 contains {10, 20, 30} and B2:B10 contains {“Red”, “Red”, “Blue”}, the result would be 20.

Example #2

MAXIFS(C2:C10, D2:D10, ">=100", E2:E10, "<200")

This function calculates the maximum value from C2:C10, subject to the conditions that the corresponding values in D2:D10 are greater than or equal to 100 and those in E2:E10 are less than 200. For instance, if C2:C10 has {150, 250, 50} and the criteria are met, it returns 150.

Example #3

MAXIFS(F2:F10, G2:G10, "Yes", H2:H10, "<50")

This example retrieves the highest value from F2:F10 where G2:G10 equals "Yes" and H2:H10 is less than 50. Given F2:F10 is {30, 40, 20}, if these conditions are satisfied for the corresponding rows, it returns 40.

Error handling

• VALUE!: This error occurs when the provided ranges are not of the same size, leading to conflicting calculations.
• N/A: This indicates that there are no matching criteria found in the specified ranges, preventing any maximum value from being returned.
• DIV/0!: Although less common in the context of MAXIFS, this could arise if calculations inadvertently lead to division by zero due to filtering.

Conclusion

In summary, the MAXIFS function is a versatile feature in Google Sheets, enabling users to efficiently determine maximum values under defined conditions. This capability is essential for data analysis, allowing for clearer insights and more informed decision-making based on specific criteria.