Highest and lowest that match with MAXIFS and MINIFS

1 / 5

We want each team's smallest sale in F2:F5. MINIFS finds it among the rows that match.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCDEFG
1NameTeamSalesTeamSmallest sale
2TariqWest50North
3NadiaWest800South
4DanielNorth290East
5QuinnSouth900West
6JamalNorth680
7IslaEast420
8KofiWest340
9YusufNorth640
10SamSouth870
11RosaWest840
12
13
14
15
team
sales
smallest sale
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How MAXIFS and MINIFS works

  • What do MAXIFS and MINIFS do?

    MAXIFS finds the highest number among the rows that match; MINIFS finds the lowest.

    =MAXIFS(max_range, criteria_range, criteria)

  • Which part comes first?

    The numbers to search come first, like SUMIFS: =MAXIFS($C$2:$C$11,$B$2:$B$11,E2) is the best sale in column C among the rows whose team in column B matches E2.

  • Can I use more than one condition?

    Yes. Add more pairs of a range and what it must match: =MAXIFS(C2:C11,B2:B11,"North",D2:D11,"Online").

  • What if nothing matches?

    Both give 0, not an error. Check the condition if you see an unexpected 0.

Know someone fighting with a spreadsheet? Send them this page.