MAXIFS, MINIFS & AVERAGEIFS practice exercises
MAXIFS, MINIFS and AVERAGEIFS work like SUMIFS, but return the largest, smallest or average value among the rows that match your conditions. They replace a lot of filtering and sorting by hand.
Syntax
MAXIFS(range, criteria_range1, criterion1, ...) · MINIFS(...) · AVERAGEIFS(...)
range- The numbers to take the max, min or average of. It comes first, like in SUMIFS.
criteria_range1, criterion1- The column to test and the condition. Add more pairs for more conditions.
The practice data
An orders table: who sold what, where, and for how much. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Price | Sales |
| 2 | Ana | East | Widget | 12 | 5 | 60 |
| 3 | Ben | West | Gadget | 5 | 20 | 100 |
| 4 | Cara | East | Gadget | 8 | 20 | 160 |
| 5 | Dev | North | Widget | 20 | 5 | 100 |
| 6 | Ana | West | Gizmo | 3 | 50 | 150 |
| 7 | Ben | East | Widget | 7 | 5 | 35 |
| 8 | Cara | North | Gizmo | 2 | 50 | 100 |
| 9 | Dev | East | Gadget | 6 | 20 | 120 |
Worked example
=MAXIFS(F2:F9,B2:B9,"East")
The largest Sales value among East orders. Result:
| 160 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return the smallest Sales value for Widget orders.
2 Return the average Sales of Widget orders in the East.
3 Return the most Units Dev sold in a single order.
Master MAXIFS, MINIFS & AVERAGEIFS in the full course
Lesson 6 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon MAXIFS, MINIFS & AVERAGEIFS mistakes
- Putting the range last, as in AVERAGEIF. In the -IFS functions it always comes first.
- AVERAGEIFS with no matching rows returns #DIV/0!. Wrap it in IFERROR if that can happen.
- Using MAX(IF(...)) array tricks from old spreadsheets when MAXIFS does it directly.
FAQ
Are MAXIFS and MINIFS available in Excel?
Yes, in Excel 2019 and later and in Excel 365. They work the same way in Google Sheets.
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF takes one condition and puts the average range last. AVERAGEIFS takes many conditions and puts it first.