Math & Statisticsintermediate
COUNTIFS for active customers by segment
=COUNTIFS($B:$B,"Active",$C:$C,$G$2)
Excel how-to guide
How to averageifs conditional average in Excel
Find the average order value for a specific region and product category, or the average score for a department in a given quarter.
Step-by-step instructions
How to averageifs conditional average — 3 steps
1
Enter the range of values to average as the first argument.
2
Add criteria range and criteria pairs for each condition — as many as you need.
3
All conditions must be true for a row to be included in the average.
Example data
Worked example
| Rep | Region | Quarter | Sale |
|---|---|---|---|
| Alice | East | Q1 | 1200 |
| Bob | West | Q1 | 950 |
| Carol | East | Q1 | 1800 |
| David | East | Q2 | 1100 |
Common mistakes
Errors to watch out for
AVERAGEIFS returns
No rows matched all the criteria, so there are no values to average. Check that the criteria values match the data exactly, including capitalisation and trailing spaces.
Criteria range and average range are different sizes
All ranges in AVERAGEIFS must have the same number of rows. Mismatched ranges cause a #VALUE error.
FAQ
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.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
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))