AVERAGEIF, MAXIFS & COUNTUNIQUE in Google Sheets
The conditional family goes beyond SUMIF and COUNTIF. AVERAGEIF averages only the rows that match: =AVERAGEIF(A2:A99, "North", C2:C99). Its argument order matches SUMIF: test range, condition, then the range to average.
MAXIFS and MINIFS return the largest or smallest value that meets one or more conditions. Like SUMIFS, the result range comes first: =MAXIFS(C2:C99, A2:A99, "North"). COUNTUNIQUE counts how many different values appear, such as distinct customers or sales reps.
Syntax
=AVERAGEIF(range, criterion, [average_range]) · =MAXIFS(range, criteria_range1, criterion1, …)
Example
=MAXIFS(C2:C99, A2:A99, E2)
The biggest sale in the region named in E2.
Common mistakes
- Putting the result range last in MAXIFS, as you would in AVERAGEIF.
- AVERAGEIF returns #DIV/0! when nothing matches: wrap it in IFERROR.
- Using COUNTA when you need distinct values (COUNTUNIQUE).
← SUMIFS & COUNTIFS · Working with dates → · All lessons