MAXIFS and MINIFS in Excel

MAXIFS returns the largest value in a range that meets one or more criteria, and MINIFS returns the smallest. They mirror SUMIFS in argument order, with the value range first followed by criteria range and criteria pairs. Analysts use them to find the peak or trough of a metric within a segment without sorting or filtering the data.

Syntax and argument order

The signatures are =MAXIFS(max_range, criteria_range1, criteria1, ...) and =MINIFS(min_range, criteria_range1, criteria1, ...). The range you take the max or min of comes first, then each criteria range pairs with its criteria.

Every criteria range must be the same size as the value range. Multiple criteria pairs combine with AND logic, so a row must satisfy all conditions to count. Criteria use the same syntax as SUMIFS, including comparison text like ">=100" and cell references joined with &.

Worked example: highest sale in a region and quarter

Amounts sit in D2:D6, regions in B2:B6, and quarters in C2:C6. You want the largest West region sale in Q2.

1. Enter =MAXIFS(D2:D6, B2:B6, "West", C2:C6, "Q2").
2. Excel scans for rows where region is West and quarter is Q2.
3. Two rows qualify, with amounts 12000 and 8000.
4. MAXIFS returns the larger of those, which is 12000. Swapping to MINIFS would return 8000.

Only West and Q2 rows count. The largest of 12000 and 8000 is 12000.

How analysts use MAXIFS and MINIFS

These functions answer best and worst questions inside a slice of data without restructuring the table.

• Find the peak monthly balance for a single account to size a facility.
• Return the lowest unit price a vendor charged in a date window.
• Pull the highest headcount in a department for a capacity check.
• Combine a date floor and ceiling as two criteria pairs to bound a period.

Availability and the older-version alternative

MAXIFS and MINIFS arrived in Excel 2019 and are available in Excel 365. In Excel 2016 and earlier they do not exist, so a file that relies on them shows a #NAME? error when opened in an older version.

For older versions, use an array formula. To replicate the example, enter =MAX(IF((B2:B6="West")*(C2:C6="Q2"), D2:D6)) and confirm with Ctrl plus Shift plus Enter so it evaluates as an array. The product of the two comparison arrays acts as the AND, returning 1 where both match, and MAX takes the largest qualifying amount. Replace MAX with MIN for the minimum.