Description
Use this function to return the maximum value among cells specified by a given set of conditions or criteria.
MAXIFS is useful for finding the largest value in a range that meets one or more specific conditions. This function is particularly helpful when analyzing data with multiple variables or categories.
Syntax
MAXIFS(max_range, criteria_range_1, criteria_1, […, criteria_range_126, criteria_126])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
max_range |
Yes | The range of cells from which you want to determine the maximum value. | A number, a reference to a cell containing a number, a cell range, or a formula which results in any of these. |
criteria_range_1 |
Yes | The range of cells to evaluate by criteria_1. |
A cell range, or a formula which results in a cell range. |
criterion_1 |
Yes | The criteria by which to evaluate criteria_range1. |
A number, a text string, a cell reference, or a formula which results in any of these. |
[criteria_range_n, criterion_n] |
No | Additional ranges and their corresponding criteria. | Same as criteria_range_1 and criterion_1. Up to 126 additional range/criteria pairs can be provided. |
Example
Sample data
| A | B | C | D |
|---|---|---|---|
| Region | Product | Sales | Quarter |
| North | Apples | 5000 | Q1 |
| South | Oranges | 4500 | Q1 |
| East | Apples | 6000 | Q2 |
| West | Bananas | 5500 | Q2 |
| North | Oranges | 4800 | Q3 |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=MAXIFS(C2:C7, B2:B7, "Apples") |
Returns the maximum sales for Apples. | 6000 |
=MAXIFS(C2:C7, A2:A7, "North", D2:D7, "Q1") |
Returns the maximum sales in the North region during Q1. | 5000 |
=MAXIFS(C2:C7, D2:D7, "Q2") |
Returns the maximum sales during Q2. | 6000 |
=MAXIFS(C2:C7, B2:B7, "<>Apples") |
Returns the maximum sales for products other than Apples. | 5500 |
=MAXIFS(C2:C7, C2:C7, ">=5000", D2:D7, "Q1") |
Returns the maximum sales of 5000 or more during Q1. | 5000 |
Notes
- MAXIFS evaluates all specified criteria ranges simultaneously.
- If no cells meet all the specified criteria, MAXIFS returns 0.
- You can use wildcards (* and ?) in text criteria.
- Criteria can include mathematical operators (=, >, <, <>, >=, <=) for more flexible conditions.
- MAXIFS ignores blank cells in
max_range. - MAXIFS is case-insensitive when evaluating text criteria.
Tips
- Use MAXIFS for complex data analysis involving multiple conditions.
- Combine MAXIFS with other functions such as MINIFS and AVERAGEIFS for comprehensive data analysis.
- When working with dates, use the DATE function in your criteria for more precise filtering.
- To find the maximum value that meets at least one of multiple criteria, combine MAX with multiple IF statements instead of MAXIFS.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA