MaxIFS in Google Sheets: A Practical Guide

What MAXIFS does in Google Sheets

MAXIFS is a core function for conditional maximums. It returns the highest value in max_range that satisfies all supplied criteria. This is particularly useful for scenarios like finding the top revenue figure for a specific product in a given region and time frame. Unlike a plain MAX plus a separate filter, MAXIFS evaluates all criteria in one pass, which makes formulas cleaner and faster on larger datasets. In Google Sheets, you can combine multiple criteria ranges to model complex rules and still obtain a single numeric result. When used correctly, MAXIFS reduces the need for helper columns and array formulas.

• The first example returns the maximum value from column B where column A equals "Widget A".
• The second example adds a second condition across different ranges, returning the max Revenue for Widget A in the West region.

Syntax and parameters

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

• max_range: The range with numeric values you want to maximize.
• criteria_range1, criteria_range2, ...: Ranges that align with max_range; each is tested against the corresponding criterion.
• criterion1, criterion2, ...: Conditions such as text strings, numbers, operators, or wildcards (e.g., ">100", "Widget*", "North").

Tips:

• All ranges must be the same size. Mismatched lengths yield an error.
• Criteria can combine exact matches, inequalities, and text patterns.

Real-world scenario: dataset example

Consider a small dataset where A2:A5 contains Product, B2:B5 Region, C2:C5 Month, and D2:D5 Revenue. We want the maximum Revenue for WidgetA in the North region.

Interpretation:

• max_range is D2:D5 (Revenue).
• First criterion selects WidgetA in A2:A5.
• Second criterion selects North in B2:B5.
• Result is the maximum Revenue among the matching rows.

Common pitfalls and fixes

Common issues

• Mismatched range lengths: max_range must align with every criteria_range. If A2:A100 has 99 entries, D2:D100 must have 99 entries as well.
• Non-numeric data in max_range: MAXIFS ignores non-numeric values, but ensuring numeric data improves reliability.
• Empty results: If no rows match, MAXIFS can return 0 depending on data; test with a controlled example to confirm behavior in your sheet.

Fixes

• Use named ranges for readability and to keep ranges aligned when data expands.
• Validate inputs with a quick check formula like =COUNTA(A2:A100) = ROWS(D2:D100).

Alternatives and advanced patterns

If you need OR-like behavior (either of multiple criteria), MAXIFS alone cannot express OR; combine with FILTER or use a nested approach.

This uses FILTER to apply multiple criteria then MAX to pick the highest value from the filtered results. Another option is a dynamic array approach with SUMPRODUCT-like logic for more complex cases, but FILTER+MAX is typically simplest and fastest in Sheets.

Note: The latter relies on implicit array behavior in Sheets.

Performance tips and best practices

• Use named ranges for max_range and criteria_ranges to simplify maintenance as your data grows.
• Prefer MAXIFS over composing MAX with FILTER for performance on large datasets, as MAXIFS is optimized for conditional maxima.
• When working with dates, ensure criteria are date literals or proper date values to avoid text comparison issues.
• Cache frequent combinations by creating a small reference table and referencing it in MAXIFS rather than repeating heavy calculations in many cells.