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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

MAXIFS takes the numbers first, then range-condition pairs.

In E2, type:

=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

    MAXIFS takes the numbers first, then range-condition pairs. In E2, type: =MAXIFS(C2:C7,A2:A7,"Acme").

    Hint. Amounts first.

  2. 2

    In E4, use MINIFS for Birch's first order date.

    Hint. Dates, not amounts.

  3. 3

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

    Hint. No rows matched Dale.

  4. 4

    In E8, find Acme's smallest order over 700 with MINIFS. Shape: =MINIFS(range, range, test, range, test).

    Hint. ">700" in the second pair.

  5. 5

    In E9, use MAXIFS for Acme's latest order date.

    Hint. Dates column, Acme.