Spreadsheet Practice

Lesson 38 of 45, Analysis

MAXIFS & MINIFS by group

Quick answer

The biggest and smallest value for each group, without sorting or filtering.

Key formula
=MAXIFS($C$2:$C$10,$A$2:$A$10,A12) (Largest deal in the region)
Pro8 minutes, 2 exercisesMAXIFSMINIFS

Watch it done

1 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

MAXIFS(max_range, criteria_range, criteria) returns the largest value among the rows that meet the condition, and MINIFS returns the smallest. They are the siblings of SUMIFS and AVERAGEIFS.

Put the groups in a column and use one formula for each measure: =MAXIFS($C$2:$C$10,$A$2:$A$10,A12) looks at the deal sizes for the region named in A12. Add more criteria pairs for more conditions.

Syntax

=MAXIFS($C$2:$C$10,$A$2:$A$10,A12)Largest deal in the region
=MINIFS($C$2:$C$10,$A$2:$A$10,A12)Smallest deal in the region
=MAXIFS(C2:C10,A2:A10,"East",C2:C10,"<20000")Largest East deal under 20,000

Watch out for

  • The max range comes first, then criteria range and criteria, in pairs.
  • All ranges must be the same size.
  • If nothing matches, MAXIFS returns 0, not an error.

The exercises

  1. Largest deal by region. The regions are listed in A13:A15. In B13, find the largest deal size for each one, using the data in A2:C10. Fill down to B15.
  2. Smallest deal by region. In C13, find the smallest deal size for each region the same way. Fill down to C15.

Questions people ask about this

Updated Checked by running: every exercise answer on this page was graded by our formula engine on .

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.

  1. Largest deal by region
  2. Smallest deal by region