Math & Statisticsintermediate
Average values by multiple criteria with AVERAGEIFS
=AVERAGEIFS(D2:D100,B2:B100,"East",C2:C100,"Q1")
Math & Statistics
Math & Statistics formulas
Add, count, average, and summarize spreadsheet data with criteria-aware formulas.
Math & Statisticsintermediate
COUNTIFS for active customers by segment
=COUNTIFS($B:$B,"Active",$C:$C,$G$2)
Math & Statisticsintermediate
Find the maximum value by criteria with MAXIFS
=MAXIFS(D2:D100,B2:B100,"East")
Math & Statisticsbeginner
Find the Nth largest value with LARGE
=LARGE(B2:B100,1)
Math & Statisticsintermediate
Measure data spread with STDEV
=STDEV(B2:B100)
Math & Statisticsintermediate
SUMIFS for monthly revenue by category
=SUMIFS($D:$D,$B:$B,$G$2,$C:$C,">="&DATE(2026,1,1),$C:$C,"<"&DATE(2026,2,1))
Frequently asked questions
Can I use comparison operators in AVERAGEIFS criteria?
Yes — use text strings like ">100" or "<="&A1 as the criteria argument. For example, AVERAGEIFS(D2:D100,C2:C100,">="&DATE(2026,1,1)) averages values with a date on or after Jan 1 2026.
Can COUNTIFS count date-based activity too?
Yes. Add another criteria range and a date condition to further narrow the result.
Is there a MINIFS function as well?
Yes — MINIFS works identically to MAXIFS but returns the smallest matching value. Both functions were introduced in Excel 2019.
Is there a SMALL function for the Nth smallest value?
Yes — SMALL(range,k) returns the kth smallest value. SMALL(B2:B100,1) returns the minimum, equivalent to MIN.