Excel Practice
Lessons Lesson 93

MAXIFS and MINIFS

About this lesson

MAXIFS returns the largest value in a range where every condition holds. MINIFS returns the smallest. Both use SUMIFS' argument order, and both return 0 when no row matches.

By the end you can

  • find the largest and smallest value that match a condition.
  • find the earliest and latest date for one customer.

Practises MAXIFS MINIFS MAX MIN SUMIFS

The idea

MAX and MIN with conditions, in the same shape as SUMIFS. The values first, then pairs of range and condition. On a date column they answer "first order" and "latest login", because the smallest serial is the earliest day.

The edge is the empty case. When nothing matches, MAXIFS and MINIFS return 0 with no complaint. So a customer with no orders looks like a customer whose largest order was free. AVERAGEIFS gives an error in the same situation, which is more truthful and more annoying. With these two, a COUNTIFS beside them says whether the 0 is real.

The mistake to watch for

A zero that means nothing matched. MAXIFS and MINIFS return 0 for no match. So a customer with no orders looks like a customer whose largest order was free. Put a COUNTIFS beside them when it matters. And remember that on a date column, the minimum is the earliest and the maximum the latest.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, Acme's largest order, with MAXIFS: the amounts first, then the customer column and the name.

To begin, type it exactly:

=MAXIFS(C2:C7,A2:A7,"Acme")

E2
Row A B C D E F G
1 Customer Date Amount Shown Acme's largest
2 Acme 45300 820 9 Jan
3 Birch 45304 410 13 Jan Birch's first order
4 Acme 45311 1250 20 Jan
5 Cedar 45315 275 24 Jan Dale's largest
6 Birch 45322 960 31 Jan
7 Acme 45330 640 8 Feb Acme's smallest over 700
8

Every step

  1. 1

    In E2, Acme's largest order, with MAXIFS: the amounts first, then the customer column and the name. To begin, type it exactly: =MAXIFS(C2:C7,A2:A7,"Acme").

    Hint. Amounts first.

  2. 2

    In E4, the date of Birch's first order: MINIFS over the date column.

    Hint. Dates, not amounts.

  3. 3

    Dale has never ordered. Read =MAXIFS(C2:C7,A2:A7,"Dale") and say what it returns.

    Hint. Nothing matched.

  4. 4

    In E8, Acme's smallest order over 700. MINIFS with two conditions, the second on the amounts themselves.

    Hint. ">700" in the second pair.

  5. 5

    In E9, write a formula using MAXIFS that gives the date of Acme's latest order.

    Hint. Dates column, Acme.