Excel MAXIFS function

Summary

The Excel MAXIFS function finds the maximum value in a range based on one or more criteria. It’s an advanced version of the MAX function, allowing for conditional checks within the data set. MAXIFS is particularly useful in scenarios where you need to identify the highest number that meets specific conditions, such as the highest sales in a particular region or the maximum score achieved by students in a certain subject.
Syntax
				
					=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
				
			
  • max_range: The range of cells from which the maximum value will be determined.
  • criteria_range1: The range of cells to evaluate with the first criteria.
  • criteria1: The condition that cells in the first criteria range must meet.
  • [criteria_range2, criteria2], …: Additional ranges and their corresponding criteria.
Return value
The largest number in the max_range that meets all the given criteria.

How to use

Specify the range from which to find the maximum value (max_range), then define one or more pairs of criteria ranges and criteria. MAXIFS evaluates the criteria against the respective ranges and returns the highest value that meets all conditions.

Examples

Simple MAXIFS
Finding the Highest Sales in a Region: To locate the highest sales figure in the East region.
				
					=MAXIFS(B2:B100, A2:A100, "East")
				
			
If B2:B100 contains sales figures and A2:A100 lists regions, and you want to find the highest sales number in the “East” region, MAXIFS returns the maximum sales figure only from those entries marked as “East”.
MAXIFS with Multiple Criteria
Identifying the Maximum Score for a Specific Subject: Calculating the highest score achieved in Math by juniors.
				
					=MAXIFS(C2:C100, B2:B100, "Math", D2:D100, "Junior")
				
			
Assuming C2:C100 holds student scores, B2:B100 the subjects, and D2:D100 the class years, this formula finds the highest Math score among juniors.
MAXIFS for Date Range
Highest Sales in a Specific Month: To determine the maximum sales figure achieved in March 2022.
				
					=MAXIFS(B2:B100, A2:A100, ">=3/1/2022", A2:A100, "<=3/31/2022")
				
			
This calculates the highest sales figure in B2:B100 for sales that occurred in March 2022, based on dates listed in A2:A100.
MAXIFS Across Different Data Types
Maximum Value with Exclusions: Finding the highest test score that is not a retake.
				
					=MAXIFS(C2:C100, D2:D100, "<>Retake")
				
			
For C2:C100 containing test scores and D2:D100 indicating whether a test was a “Retake”, this formula returns the highest score among the original attempts only.

Additional Notes

  • MAXIFS requires at least one criteria range and criteria pair but can handle multiple conditions for more complex queries.
  • Introduced in Excel 2016, MAXIFS is not available in earlier versions of Excel. As an alternative for those versions, combinations of IF and MAX functions within an array formula can serve a similar purpose.

Related Functions

Excel MINIFS function

The Excel MINIFS function finds the minimum value among cells specified by criteria, enhancing targeted data examination.

Excel MAX function

The Excel MAX function finds the highest value in a set, key for identifying peak data points.

Excel MIN function

The Excel MIN function identifies the lowest value in a data set, crucial for determining minimums in analyses.

Content Navigation