MAXIFS Function in Excel: Largest Value Meeting Conditions

📎 This article includes 1 downloadable practice file ↓

StatisticalLevel: IntermediateAvailable in: Excel 2019+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. MAXIFS in practice
6. Where you will use it
7. Worked example
8. Mistakes people make
9. Related functions
10. Continue learning

MAXIFS returns the maximum of values where all conditions are met; MINIFS returns the minimum.

Syntax

=MAXIFS(max_range, criteria_range1, criteria1, ...)

Argument	What it means
max_range	Values to check.

Examples

Example 1

=MAXIFS(E:E, B:B, "North")

Biggest North sale.

Example 2

=MAXIFS(A:A, C:C, F2)

Last order date for the customer in F2.

MAXIFS in practice

Where you will use it

• Biggest order per region
• Latest date per customer
• Top score in a class

Worked example

Region	Sale	Formula	Result
North	12000	=MAXIFS($B$2:$B$4,$A$2:$A$4,A2)	15000
South	8000	=MAXIFS($B$2:$B$4,$A$2:$A$4,A3)	8000
North	15000	=MAXIFS($B$2:$B$4,$A$2:$A$4,A4)	15000

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
No match gives 0	Check COUNTIFS first if 0 is possible data
Older Excel	MAXIFS needs Excel 2019 or 365

Related functions

MINIFS · SUMIFS · MAX

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Statistical guides →

GAMMAINV Function in Excel: Inverse Gamma (Older Name)GAMMAINV returns the value at a gamma probability. It is a compatibility function: Excel…GAMMADIST Function in Excel: Gamma Distribution (Older Name)GAMMADIST returns the gamma probability. It is a compatibility function: Excel keeps it so…TTEST Function in Excel: t-Test (Older Name)TTEST returns the p-value of a t-test between two samples. It is a compatibility…GAMMA.INV Function in Excel: Value for a Gamma ProbabilityStatisticsLevel: ExpertAvailable in: Excel 2010+GAMMA.INV returns the value at a given gamma probability.Syntax=GAMMA.INV(probability, alpha.

📎 Practice files for this article

• 📗
  Practice workbook: Statistics basics22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 34 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.