MAXIFS Function

Returns the largest value among cells that meet one or more conditions.

Category

Statistical

Available in

Excel 2019+, Microsoft 365

Returns

A number

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Conditional Summaries module

Overview

MAXIFS returns the largest value in one range, looking only at rows where every condition is true.

Criteria can be a value ("North", 50000), a comparison in quotes (">=50000", "<>North"), a comparison joined to a cell (">="&F2), or a pattern with wildcards — * for any characters, ? for one character ("A*", "?d"). Text matching ignores upper/lower case.

Syntax

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

Arguments

ArgumentRequired?What it does
max_rangeYesThe cells to find the largest value in.
criteria_range1YesThe first range to test, the same size as max_range.
criteria1YesThe condition for criteria_range1.
criteria_range2, criteria2, …NoUp to 126 more range/condition pairs.

Examples

Example 1 — Best result per region

The last one adds a second condition: reps whose name starts with B.

E2fx=MAXIFS(C2:C8,B2:B8,"North")
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000North62000
3BenSouth48500East71250
4CyNorth50000South, B…48500
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=MAXIFS(C2:C8,B2:B8,"North") Explain

Common mistakes

  • 0 when nothing matches. If no row meets the conditions, MAXIFS returns 0 — not an error. Check with COUNTIFS if 0 could be a real answer.
  • #VALUE! max_range and the criteria ranges are different sizes.
  • Comparison without quotes. =COUNTIF(C2:C8,>50000) is an error. Write ">50000" in quotes, or ">"&F2 to use a cell.
  • Putting the cell inside the quotes. ">F2" compares with the text "F2". Join the cell instead: ">"&F2.

Notes

  • In older versions, use =MAX(IF(B2:B8="North",C2:C8)) entered with Ctrl+Shift+Enter.
  • Related functions: MINIFS, MAX, SUMIFS, AVERAGEIFS.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.