ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
📈 MAXIFS & MINIFS — Extremes with Multiple Criteria
3
Free companion workbook from sheetsandcells.com · 9 sheets · Same 24-row sales log as the SUMIFS/COUNTIFS/AVERAGEIFS workbooks · Includes The Spread (MAX - MIN) and Full IFS Family Reconciliation (all 5 functions on same filter).
4
5
⚠️ Version requirement: MAXIFS and MINIFS require Excel 2019, Excel 365, or Excel for the web. Earlier Excel versions (2016 and older) do NOT support these functions and will show a NAME error. For Excel 2007-2016, use the {=MAX(IF(...))} array formula workaround (documented below).
6
7
8
9
Syntax (identical for both functions)
10
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
11
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
12
13
ArgumentTypeWhat it does
14
max_range / min_rangeREQUIREDThe column to search for the maximum/minimum. First argument.
15
criteria_range1REQUIREDFirst column to filter on. Same size as max_range/min_range.
16
criteria1REQUIREDValue or expression. Text, number, cell reference, or comparison like ">100".
17
criteria_range2OPTIONALSecond filter column. Up to 126 additional pairs (total 127).
18
criteria2OPTIONALAll criteria must be true for a row to be considered (AND logic).
19
20
Behavior on zero matches: MAXIFS and MINIFS return 0 when no rows match (like SUMIFS/COUNTIFS). This is different from AVERAGEIFS, which returns DIV/0. But watch out — a real 0 result and a zero-match result look identical. If your data can legitimately include 0, wrap in a COUNTIFS check to distinguish empty results from real zeros.
21
22
23
24
25
What's inside — 8 example sheets
26
27
DataSales Log — 24-row Q1 2026 sales log. Identical to the SUMIFS/COUNTIFS/AVERAGEIFS workbooks.
28
Example 1Basic MAXIFS + MINIFS — Biggest and smallest deal in each region. Side-by-side comparison.
29
Example 2Multi Criteria — Biggest North+Widget A deal. Smallest South+Gizmo transaction. AND logic.
30
Example 3Date Range Extremes — Biggest/smallest deal per month. Reveals volatility SUMIFS averages hide.
31
Example 4Wildcards & Patterns — Biggest Widget-anything deal. Smallest Gizmo transaction.
32
Example 5Comparison Operators — Biggest deal in the >$5K bucket. Smallest above threshold.
33
SpreadThe MAX - MIN Pattern — Compute deal spread per region: how volatile is each? Unique to this pair.
34
ReconFull IFS Family Reconciliation — All 5 IFS functions side by side. Complete statistical summary — SUM, COUNT, AVG, MAX, MIN.
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100