📎 This article includes 2 downloadable practice files ↓
In this article
AVERAGEIFS averages values where all conditions are true.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)| Argument | What it means |
|---|---|
average_range |
Cells to average. |
Examples
Example 1
=AVERAGEIFS(E:E, B:B, "North", C:C, "Retail")Average North retail order.
Common errors and fixes
| You see | Why, and the fix |
|---|---|
#DIV/0! |
No rows match. Wrap in IFERROR. |
AVERAGEIFS in practice
Where you will use it
- Average ticket size for North in Q2
- Average marks of girls above 75% attendance
- Average claim amount for approved travel claims
Worked example
Average North sale for the month in each row.
| Region | Month | Sale | Formula | Result |
|---|---|---|---|---|
| North | Apr | 12000 | =AVERAGEIFS($C$2:$C$4,$A$2:$A$4,"North",$B$2:$B$4,B2) | 12000 |
| North | May | 18000 | =AVERAGEIFS($C$2:$C$4,$A$2:$A$4,"North",$B$2:$B$4,B3) | 18000 |
| South | Apr | 9000 | =AVERAGEIFS($C$2:$C$4,$A$2:$A$4,"North",$B$2:$B$4,B4) | 12000 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Putting the average range last (as in AVERAGEIF) | In AVERAGEIFS the average range comes first |
| Criteria as numbers without quotes | Use ">=40" with quotes, or ">="&E1 |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
⬇ Download all 2 files (ZIP · 38 KB)
- 📗SUMIFS/COUNTIFS scenarios workbookConditional sums and counts with dates, OR logic and wildcards, plus 4 classic reasons totals come out wrong — all with PASS/FAIL checks.⬇ XLSX · 9 KB
- 📗Practice workbook: Statistics basics22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 34 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong