AVERAGEIF finds the average of values that meet a condition, such as the average salary of one department.
AVERAGEIF syntax
=AVERAGEIF(range, criteria, [average_range])
Example: average sales for the North region.
=AVERAGEIF(A2:A100, "North", C2:C100)
AVERAGEIFS for several conditions
=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2)
Example: average sales in the North region for Laptops.
=AVERAGEIFS(C2:C100, A2:A100, "North", B2:B100, "Laptop")
Notice that in AVERAGEIFS the average range comes first.
Ignore zeros
=AVERAGEIF(C2:C100, "<>0") leaves out zeros so they do not pull the average down.
#DIV/0! error
This appears when nothing matches the condition. Wrap the formula: =IFERROR(AVERAGEIF(...), 0).
Good to know
- Blank cells and text in the average range are ignored.
- Wildcards like
"Pro*" work for text conditions.
Worked example
=AVERAGEIF(A2:A100,"North",B2:B100) gives the average sale in North. With two conditions: =AVERAGEIFS(B2:B100,A2:A100,"North",C2:C100,"Pen"). The average range comes first in AVERAGEIFS.
Common mistakes
- Wrong argument order between AVERAGEIF and AVERAGEIFS.
- Getting #DIV/0! when nothing matches. Wrap in IFERROR.
- Blank cells in the average range are ignored, which may change the result.
Frequently asked questions
Why #DIV/0!?
No row matched the condition. Use IFERROR to show a message.
Does AVERAGEIF count zeros?
Yes. Blank cells are skipped, but zeros are included.
Can I use wildcards?
Yes, for example "N*".
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: AVERAGEIF, AVERAGEIFS, AVERAGE.
Start Excel practice → Case studies
