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.

ABCDEF
1RepRegionProductUnitsPriceSales
2AnaEastWidget12560
3BenWestGadget520100
4CaraEastGadget820160
5DevNorthWidget205100
6AnaWestGizmo350150
7BenEastWidget7535
8CaraNorthGizmo250100
9DevEastGadget620120

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.

H2

2 Return the average Sales of Widget orders in the East.

H2

3 Return the most Units Dev sold in a single order.

H2

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 pricing

Common MAXIFS, MINIFS & AVERAGEIFS mistakes

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.

Keep practicing