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)
Watch it done
1 stepsWatch 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,000Watch 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
- 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.
- 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
- How to find the latest date for each customer in Excel
- How to find the first clock-in time for each day in Excel
- How to find the earliest date in a list in Excel
- How to find the top seller in each region in Excel
- How to find the next highest value in a list in Excel
- How to look up the price in effect on a date in Excel
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.
- Largest deal by region
- Smallest deal by region