fx
FunctionPedia
Quick Answer

MAXIFS Function

Finds the largest value where multiple conditions are all true.

✓ Excel✓ Google SheetsExcel Excel 2019+

Syntax

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

Parameters

ParameterDescriptionRequired
max_rangeThe range of cells from which the maximum is returned.Required
criteria_range1The first range tested against criteria1; must be the same size as max_range.Required
criteria1The condition for criteria_range1 (e.g. "Apple", ">100", "East").Required
criteria_range2, criteria2, ...Optional additional range/criteria pairs; all must be met (AND logic).Optional

Basic Example

Largest sale for a specific product

=MAXIFS(C2:C10, A2:A10, "Apple")
Result950

Among rows where column A is Apple, the largest value in column C (e.g. 950) is returned.

Advanced Examples

Example 1: Maximum with two criteria

Top sale in a region above a threshold

Both conditions must be satisfied (AND logic).

=MAXIFS(C2:C100, A2:A100, "East", B2:B100, ">1000")
Result: 1800
Only rows where region is East AND column B exceeds 1000 are considered; the largest such column C value is 1800.

Example 2: Criterion built from a cell reference

Reusable threshold in a cell

Concatenate a comparison operator with a cell value.

=MAXIFS(C2:C50, A2:A50, "Banana", B2:B50, ">"&E1)
Result: 1200
If E1 holds 500, this finds the largest column C for Banana where column B is greater than 500, here 1200.

How MAXIFS Works

MAXIFS evaluates each criteria_range against its criteria, keeping only the rows where all conditions are true. It then returns the maximum value from max_range at those rows. All ranges must have the same dimensions. If no row satisfies every criterion, MAXIFS returns 0. Criteria support operators (>, <, >=, <=, <>) and wildcards (*, ?) for text. It is the conditional counterpart of MAX, similar to how SUMIFS extends SUM.

1
Select the result cell
Click where you want the maximum.
2
Enter the formula
Type =MAXIFS(max_range, criteria_range1, criteria1, ...).
3
Press Enter
The conditional maximum appears.

Important Notes & Limitations

  • All ranges must be the same size; mismatched sizes cause #VALUE!.

  • Applies AND logic only; OR logic across criteria requires separate formulas combined with MAX.

  • Returns 0 when no row meets all criteria, which can be confused with a real zero.

Common Errors & Fixes

#VALUE! errorThe criteria ranges and max_range have different sizes or shapes.

Fix: Ensure every range covers the same rows and columns.

Wrong maximum returnedCriteria text does not exactly match, including case differences or stray spaces.

Fix: Use exact text, trim spaces, or use wildcards for partial matches.

Download Practice File

Practice MAXIFS with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the MAXIFS function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Can MAXIFS use OR logic?
No, it only applies AND across criteria pairs. For OR, compute separate MAXIFS and wrap them in MAX, or use MAX with FILTER.
Why do I get 0?
No row satisfied all criteria, and MAXIFS returns 0 in that case. Check your criteria and ranges.
Which Excel versions have MAXIFS?
MAXIFS was introduced in Excel 2019 and is also available in Microsoft 365; it is absent in Excel 2016 and earlier.