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“
Table of Contents
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.
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: Useful Excel Keyboard Shortcuts and Function Keys for Windows
