MAXIFS function in Excel – find max value with multiple criteria

This tutorial demonstrates how to use Excel's MAXIFS function to efficiently find the maximum value based on specified conditions. Before MAXIFS, users relied on complex MAX IF array formulas. This new function simplifies this process significantly.

Excel MAXIFS Function: Syntax and Basic Usage

The MAXIFS function identifies the largest numeric value within a range, subject to one or more criteria. Its syntax is:

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Where:

• max_range: The cell range containing the values from which to find the maximum.
• criteria_range1: The first range to evaluate against criteria1.
• criteria1: The condition applied to criteria_range1. This can be a number, text, or expression.
• criteria_range2, criteria2,...: (Optional) Additional ranges and their corresponding criteria. Up to 126 pairs are supported.

MAXIFS is available in Excel 2019, Excel 2021, and Microsoft 365 (Windows and Mac).

For example, to find the tallest football player (heights in D2:D11, sports in B2:B11):

=MAXIFS(D2:D11, B2:B11, "football")

For greater flexibility, specify the sport in a cell (e.g., G1):

=MAXIFS(D2:D11, B2:B11, G1)

Important: max_range and criteria_range must be the same size and shape; otherwise, a #VALUE! error results.

Excel MAXIFS: Formula Examples

MAXIFS is straightforward but has nuances. The following examples illustrate its capabilities:

1. Multiple Criteria:

To find the tallest junior basketball player:

• max_range: Heights (D2:D11)
• criteria_range1: Sports (B2:B11)
• criteria1: "basketball" (or cell G1)
• criteria_range2: School type (C2:C11)
• criteria2: "junior" (or cell G2)

Formula (hardcoded criteria):

=MAXIFS(D2:D11, B2:B11, "basketball", C2:C11, "junior")

Formula (using cell references):

=MAXIFS(D2:D11, B2:B11, G1, C2:C11, G2)

For reusable formulas, use absolute references (e.g., $D$2:$D$11).

To find the name of the tallest player, combine MAXIFS with INDEX and MATCH:

=INDEX($A$2:$A$11, MATCH(MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2), $D$2:$D$11, 0))

2. Logical Operators:

Use logical operators (>, =, ) for numeric criteria. Enclose operators and numbers in quotes (">=14"). For cell references, concatenate (e.g., ">"&G1).

To find the max height for boys aged 13-14 (age in column C):

=MAXIFS(D2:D11, C2:C11, ">=13", C2:C11, " or using cell references G1 and H1: <code>=MAXIFS(D2:D11, C2:C11, ">="&G1, C2:C11, "

To exclude volleyball:

=MAXIFS(D2:D11, B2:B11, "volleyball") or using cell reference G1: =MAXIFS(D2:D11, B2:B11, ""&G1)

3. Wildcard Characters:

Use ? (any single character) and * (any sequence) for partial matches.

To find the tallest player in games ending in "ball":

=MAXIFS(D2:D11, B2:B11, "*ball") or using cell reference G1: =MAXIFS(D2:D11, B2:B11, "*"&G1)

4. Date Ranges:

Dates are serial numbers. Use date formats Excel understands (e.g., "1-Jan-2004", "1/1/2004", or the DATE function).

To find the max height for those born in 2004 (birth date in column C):

=MAXIFS(D2:D11, C2:C11, ">="&DATE(2004,1,1), C2:C11, " or using cell reference G1: <code>=MAXIFS(D2:D11, C2:C11, ">="&DATE(G1,1,1), C2:C11, "

5. OR Logic with Multiple Criteria:

MAXIFS uses AND logic. For OR logic, use nested MAXIFS functions with MAX:

=MAX(MAXIFS(D2:D11, B2:B11, "basketball"), MAXIFS(D2:D11, B2:B11, "football")) or using cell references F1 and H1: =MAX(MAXIFS(D2:D11, B2:B11, F1), MAXIFS(D2:D11, B2:B11, H1))

Key Considerations for Excel MAXIFS:

1. Handles one or multiple criteria.
2. Uses AND logic by default.
3. max_range and criteria_range must be the same size and shape.
4. Case-insensitive for text criteria.
5. Use absolute references when copying formulas.
6. Correct criteria syntax is crucial (use quotes appropriately).
7. Available in Excel 2019 and later.

This comprehensive guide empowers you to leverage the power of the MAXIFS function for efficient data analysis in Excel.

The above is the detailed content of MAXIFS function in Excel – find max value with multiple criteria. For more information, please follow other related articles on the PHP Chinese website!