Use of MAXIFS function in Excel/ Spreadsheet

This article describes the formula syntax and usage of MAXIFS function in Excel/ Spreadsheet.

The MAXIFS function returns the maximum value among cells specified by a given set of conditions or criteria. Note: This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.

Before the start, do have a look on “What’s new in Excel 2019 for Windows“

Description of MAXIFS Function

The MAXIFS function in Excel returns the largest numeric value that meets one or more criteria in a range of values. MAXIFS can be used with some criteria like, based on dates, numbers, text, and other conditions. MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

The syntax of MAXIFS Function in Excel

= MAXIFS (max_range, range1, criteria1, [range2], [criteria2], …)

Argument	Description
max_range (required)	Range of values used to determine maximum
range1 (required)	The first range to evaluate
criteria1 (required)	The criteria to use on range1
range2 (optional)	[optional] The second range to evaluate
criteria2 (optional)	[optional] The criteria to use on range2

Remarks

Get maximum value with criteria

Return value

Maximum value

Examples

Example

In the example, we find the maximum marks obtained by a Female or a Male.

In the example shown, the formula in I5 is:

=MAXIFS(D5:D15,E3:E15,"Female")

Points to note

• Criteria range(s) must be the same size and shape as the max_range.
• The MAXIFS function was introduced in Excel 2016.
• In earlier versions of Excel you can use an array formula based on MAX and IF to find minimum values with criteria.

Read Also: Create a PivotTable to analyze worksheet data

Related: MINIFS function in Excel

If you have any doubt, feel free to contact.