MAXIFS and MINIFS: highest and lowest with a condition
The biggest order from one customer, or the earliest date in one region. MAXIFS and MINIFS with one and two conditions, and what happens with no match.
MAXIFS returns the largest number in a range where your conditions are true. MINIFS returns the
smallest. =MAXIFS(D2:D9,A2:A9,"Rosa Cafe") gives the biggest
order from Rosa Cafe.
Both need Excel 2019 or later, Microsoft 365 or Excel for the web. In Excel 2016 and older they
show #NAME?. There is a formula for older versions near the end.
The order list
A bakery sells bread to cafes and hotels. This is its order list for January, and one early February order. The sales office wants a few answers from it.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Customer | Region | Date | Amount |
| 2 | Rosa Cafe | North | 5 Jan 2026 | 240 |
| 3 | Bean Bar | South | 7 Jan 2026 | 180 |
| 4 | Rosa Cafe | North | 12 Jan 2026 | 410 |
| 5 | Oak Deli | East | 13 Jan 2026 | 95 |
| 6 | Bean Bar | South | 20 Jan 2026 | 520 |
| 7 | Hill Hotel | North | 22 Jan 2026 | 760 |
| 8 | Oak Deli | East | 27 Jan 2026 | 130 |
| 9 | Rosa Cafe | North | 3 Feb 2026 | 305 |
The shape is =MAXIFS(numbers, range, condition). The numbers
come first. Then comes a range to test and the condition for it. This is the same order as
SUMIFS.
The biggest and smallest order from one customer
=MAXIFS(D2:D9,A2:A9,"Rosa Cafe") gives 410.
=MINIFS(D2:D9,A2:A9,"Rosa Cafe") gives 240.
Rosa Cafe has 3 orders: 240, 410 and 305. MAXIFS looks only at those 3 and returns the largest. MINIFS returns the smallest.
Do not type the name into every formula. Put it in a cell, say F2, and point at it.
=MAXIFS(D2:D9,A2:A9,F2) with Bean Bar in F2 gives 520. Change
F2 and the answer changes.
The first and latest order in one region
A date is a number in Excel, and a later date is a bigger number. So MINIFS on a date column finds the earliest date. MAXIFS finds the latest.
=MINIFS(C2:C9,B2:B9,"South") gives 7 January 2026. That is
the first order in the South.
=MAXIFS(C2:C9,B2:B9,"North") gives 3 February 2026. That is the
last order in the North.
If the answer shows as 46029, the cell is not formatted as a date yet. Format it as a date and it shows 7 January 2026.
Two conditions
Add more pairs of range and condition. A row counts only when every condition is true.
The biggest North order in January:
=MAXIFS(D2:D9,B2:B9,"North",C2:C9,"<"&DATE(2026,2,1))
gives 760.
The condition on the date is before 1 February. The operator goes in quotes, and the & joins it to the date.
The smallest North order over 300:
=MINIFS(D2:D9,B2:B9,"North",D2:D9,">300") gives 305.
The numbers can be tested too. Here D2:D9 is both the numbers and one of the ranges to test.
When nothing matches, the answer is 0
Mill Inn is a new customer. It has not ordered yet. Here is what 3 similar functions return for it.
-
=MAXIFS(D2:D9,A2:A9,"Mill Inn")gives 0. -
=MINIFS(D2:D9,A2:A9,"Mill Inn")gives 0. -
=AVERAGEIFS(D2:D9,A2:A9,"Mill Inn")gives#DIV/0!.
The 0 is the problem. There is no error, so nobody looks twice. In a report, Mill Inn's biggest order looks like an order of 0.
On a date column it looks stranger.
=MINIFS(C2:C9,B2:B9,"West") gives 0, because there is no West
region. Formatted as a date, 0 shows as day 0 of January 1900.
The fix is to count first. If the count is 0, show a message instead of the 0.
=IF(COUNTIFS(A2:A9,"Mill Inn")=0,"No orders",MAXIFS(D2:D9,A2:A9,"Mill Inn"))
This gives No orders. For Rosa Cafe the same formula gives 410, because the count is 3.
You do not always need this. If every name in your report has orders, a plain MAXIFS is fine. Add the check when the list of names comes from somewhere else.
Two common mistakes
Ranges of different sizes.
=MAXIFS(D2:D9,A2:A8,"Rosa Cafe") gives
#VALUE!. D2:D9 has 8 rows and A2:A8 has 7. Every range must be
the same size.
A condition without quotes. Text and operators go inside quotes, like "North"
or ">300". A cell address does not. Write ">"&F2,
not ">F2". The second one compares each number with the text F2. Nothing
matches, so you get 0.
Questions people ask
What do I use in Excel 2016 or older?
Use MAX with IF: =MAX(IF(A2:A9="Rosa Cafe",D2:D9)). It gives
410. In older versions you press Ctrl+Shift+Enter to finish it, not only Enter. In Microsoft 365,
Enter is enough.
Can I get the customer's name, not the amount?
Not with MAXIFS alone. It returns a number. Use it to find the amount, then look that amount up. Two orders with the same amount can give the wrong row, so check for ties.
Is there an AVERAGEIFS for averages?
Yes, with the same shape. =AVERAGEIFS(D2:D9,A2:A9,"Rosa Cafe")
gives about 318.33. Remember it gives #DIV/0! when nothing
matches, not 0.
MAXIFS uses the same conditions as SUMIFS and COUNTIFS. The COUNTIF and COUNTIFS article has more practice with them. In the lessons below, you type each formula into a real sheet. Your answer is checked when you press Enter.