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 | ||
|---|---|---|---|---|---|
1 |
Region | Product | Sales | Quarter | |
2 |
North | Apples | 5000 | Q1 | |
3 |
South | Oranges | 4500 | Q1 | |
4 |
East | Apples | 6000 | Q2 | |
5 |
West | Bananas | 5500 | Q2 | |
6 |
North | Oranges | 4800 | Q3 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the highest value in a range that matches a single category. | =MAXIFS(C2:C7, B2:B7, "Apples") |
Finds the highest sales figure among rows where the product is "Apples." Result: 6000 |
| Find the highest value in a range that matches two conditions at once. | =MAXIFS(C2:C7, A2:A7, "North", D2:D7, "Q1") |
Finds the highest sales figure among rows that are both "North" and "Q1" — only one row qualifies. Result: 5000 |
| Find the highest value in a range that matches a single condition. | =MAXIFS(C2:C7, D2:D7, "Q2") |
Finds the highest sales figure among rows from "Q2." Result: 6000 |
| Find the highest value in a range while excluding a specific category. | =MAXIFS(C2:C7, B2:B7, "<>Apples") |
Finds the highest sales figure among products other than "Apples." Result: 5500 |
| Find the highest value in a range that itself meets a numeric condition, combined with another condition. | =MAXIFS(C2:C7, C2:C7, ">=5000", D2:D7, "Q1") |
Finds the highest sales figure among values already 5000 or more, restricted to "Q1." Result: 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.