How to Use the MAXIFS Function in Excel: Step‑by‑Step Guide

1. What Is MAXIFS?

MAXIFS returns the largest numeric value in a range that satisfies one or more conditions. It works like MAX, but lets you filter the data without creating helper columns.

Syntax	Description
`MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)`	max_range – numbers to evaluate. criteria_rangeN – range to test. criteriaN – condition such as `"East"` or `">=100"`. Additional pairs can be added for extra criteria.

Key points

Availability – Excel 365, Excel 2021, and later. In Excel 2019 you can use an array formula with MAX + IF.
Data types – max_range must contain numbers; text is ignored. Criteria may be numbers, text, dates, or logical expressions.
Logic – All criteria are combined with AND; a row must meet every condition.
Blanks – Blank cells in max_range are ignored. Blank cells in a criteria range are treated as empty strings ("").

2. Basic Example

A (Region)	B (Sales)
East	1200
West	950
East	1500
North	800
East	1100

=MAXIFS(B2:B6, A2:A6, "East")

Result: 1500 – the highest sales figure for the East region.

3. Syntax and Parameters

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

Argument	Description	Required?
max_range	Cells containing the numbers to evaluate.	Yes
criteria_range1	First range to test; must be the same size as max_range.	Yes
criteria1	Condition applied to criteria_range1 (e.g., `">=10"`, `"Apple"`).	Yes
criteria_range2, criteria2, …	Optional additional pairs; up to 127 pairs are allowed.	No

Important reminders

All ranges must be identical in height and width; otherwise Excel returns #VALUE!.
Text criteria that use wildcards (*, ?) must be quoted, e.g., "A*" for any text starting with “A”.
Google Sheets does not have a native MAXIFS; you can mimic it with MAX(FILTER(...)).

4. Using Multiple Criteria

All criteria are ANDed together. The function evaluates only rows where every condition is true.

Example: Highest sales for Widget in the North region

A (Region)	B (Product)	C (Sales)
North	Widget	120
South	Gadget	95
North	Gadget	130
East	Widget	85
North	Widget	150
South	Widget	110

=MAXIFS(C2:C7, A2:A7, "North", B2:B7, "Widget")

Result: 150

If you need the same result in an older version of Excel, use an array formula:

=MAX(IF((A2:A7="North")*(B2:B7="Widget"), C2:C7))

Enter with Ctrl+Shift+Enter .

5. Common Use Cases

Scenario	Sample Data	Formula	Result
Single‑product max sales	`Product \| Sales` Apple	120 Banana	85 Apple
Top score per department	`Dept \| Score` HR	78 IT	92 HR
Latest closed date in West	`Region \| Status \| Date` West	Closed	2023‑04‑01 East

Tip: Replace the literal criteria with cell references (e.g., D1) to make the formula dynamic.

6. Troubleshooting & Pitfalls

Issue	Why It Happens	Fix
`#NAME?`	Excel 2016 or older (no MAXIFS).	Use the array‑formula alternative shown above.
`#VALUE!`	Mismatched range sizes or incompatible data types.	Ensure every `criteria_range` matches `max_range` exactly; clean non‑numeric data with `VALUE` or `TRIM`.
Incorrect comparison	Criteria like `>100` were entered without quotes.	Write the condition as a string: `">100"` or concatenate: `">"&D2`.

7. Advanced Techniques with Dynamic Arrays (Excel 365/2021)

7.1 Return the maximum per category

=LET(
    cats, UNIQUE(A2:A20),
    maxVals, MAP(cats, LAMBDA(c, MAXIFS(B2:B20, A2:A20, c))),
    HSTACK(cats, maxVals)
)

Category	Max Sales
Fruit	9 800
Veggie	7 200

7.2 Top N values that meet a condition

=LET(
    filtered, FILTER(B2:B20, C2:C20="East"),
    sorted, SORT(filtered,,-1),
    TAKE(sorted,5)
)

Returns the five largest “East” sales, spilling vertically.

7.3 Running maximum down a column

=SCAN(0, B2:B20, LAMBDA(prev, cur, MAX(prev, cur)))

Creates a column that shows the highest sales up to each row.

8. FAQ

What versions of Excel support MAXIFS?
MAXIFS is built into Excel 365, Excel 2021, and Excel 2019 (with the dynamic‑array update). Earlier versions (Excel 2016 and older) do not have the function and require an array formula using MAX + IF.

How does MAXIFS differ from MAX and IF?
MAX returns the largest value in a range, ignoring any conditions. IF can test a condition but returns a value, not an aggregate. MAXIFS combines both ideas: it evaluates a condition (or several) and then returns the maximum of the cells that meet those conditions.

Can I use MAXIFS with text criteria?
Yes. Text criteria can be literal strings (e.g., "East"), wildcards ("A*"), or cell references that contain the text. The criteria must be quoted unless they are supplied from a cell.

Is MAXIFS available in Google Sheets?
Yes. Google Sheets has a native MAXIFS with the same syntax, e.g. =MAXIFS(B2:B10, A2:A10, "East").

How should I handle errors returned by MAXIFS?
#VALUE! usually means a range‑size mismatch; check that every criteria_range matches max_range. #NAME? indicates the function isn’t recognized—verify you’re using a supported Excel version. Use IFERROR or IFNA to provide a fallback value, e.g., =IFERROR(MAXIFS(...),0).

Bottom line: MAXIFS simplifies conditional maximum calculations, improves readability, and (when paired with dynamic‑array functions) can power sophisticated analyses without complex helper columns. Use it wherever you need the “largest value that meets these rules.”