Level 6 · Biggest and smallest promo order with MAXIFS

SUMIFS adds up the matching rows and AVERAGEIFS averages them. Two more members of the family pick the extremes: MAXIFS returns the largest matching value and MINIFS the smallest. Combined with date criteria, they answer “what was our biggest order during the sale?” in one cell.

MAXIFS and MINIFS

=MAXIFS(max_range, range1, criteria1, range2, criteria2, …)
=MINIFS(min_range, range1, criteria1, …)

The argument order is the SUMIFS order: the range to search first, then the pairs. For the largest completed order on the whole sheet:

=MAXIFS(D2:D59,C2:C59,"completed")

That gives 5,216.96, an order from November 18, before the promo started. Frank wants the promo only, so the formula needs a date window.

A date window from two cells

Dates in a spreadsheet are numbers: each day is one more than the day before. That means the usual operators work on them. “From G1 to G2, both days included” is two conditions on the same column:

B2:B59, ">="&G1,   B2:B59, "<="&G2

As in level 4, the operator stays in quotes and the cell is joined with &. Typing a date inside the quotes, like ">=2025-11-28", can work, but how Excel reads typed dates depends on the computer’s regional settings. A date in a cell is the same everywhere.

Put together, with the status in G3:

G5   =MAXIFS(D2:D59,C2:C59,G3,B2:B59,">="&G1,B2:B59,"<="&G2)
G6   =MINIFS(D2:D59,C2:C59,G3,B2:B59,">="&G1,B2:B59,"<="&G2)

G5 shows 2,218.48 and G6 72.71.

Every condition earns its place

Try removing one at a time and watch G5:

Conditions	Largest order
status and both dates	2,218.48
both dates, no status	3,149.12 (a cancelled order)
status, no dates	5,216.96 (November 18)

A number that looks impressive is often a filter that’s missing. When an extreme surprises you, find the row it comes from before you report it. =XLOOKUP(G5,D2:D59,A2:A59) from chapter 2 returns the order id: 12161, placed on November 29.

When nothing matches

MAXIFS and MINIFS return 0 when no row passes the tests, not an error. Set G3 to pending: there are no pending orders in this window, and both cells show 0. A “smallest order of 0” is a sign the filter found nothing, so check the inputs before you read it as a free order. AVERAGEIFS is more honest here, with #DIV/0!.

Common mistakes

• Dates with times. This sheet stores plain dates. If order_date held a time too, "<="&G2 would stop at midnight at the start of December 1 and drop that day’s orders. Use "<"&G2+1 (before the next day) for timestamps.
• > instead of >=. ">"&G1 silently drops the first day of the promo, Black Friday itself.
• Older Excel. MAXIFS and MINIFS arrived in Excel 2019; Microsoft 365 and Google Sheets have them. In Excel 2016 they show #NAME?.
• Quotes around the cell. ">=G1" compares with the text G1 and nothing matches.