How to use AVERAGEIF in Excel
Averages values whose row matches a condition.
Syntax
rangeThe cells to testcriteriaThe condition[average_range]The cells to average optionalExample
Here’s AVERAGEIF on a small table of orders. The highlighted cells are the ones the formula reads.
=AVERAGEIF(C2:C9,"Lena",F2:F9)Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with AVERAGEIF
No matches gives #DIV/0!
If no rows match, there’s nothing to average. Wrap it in IFERROR to show a friendlier result.
Conditions go in quotes
Write "East" or ">3", not East or >3.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=AVERAGEIF(C2:C9,"Lena",F2:F9
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Text without quotes
=AVERAGEIF(C2:C9,Lena,F2:F9)
Text needs quotes. Lena isn’t a function or a cell, so Excel can’t read it. If you mean the word, write "Lena" with quotes.
Questions
What does the AVERAGEIF function do in Excel?
Averages values whose row matches a condition. The syntax is AVERAGEIF(range, criteria, [average_range]).
Why is my AVERAGEIF formula giving the wrong answer?
No matches gives #DIV/0!: If no rows match, there’s nothing to average. Wrap it in IFERROR to show a friendlier result. Conditions go in quotes: Write "East" or ">3", not East or >3.
Does AVERAGEIF work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.
Related functions
- COUNTIFCounts cells that match a condition.
- COUNTIFSCounts rows that match every condition.
- SUMIFAdds values whose row matches a condition.
- SUMIFSAdds values whose row matches every condition.
- AVERAGEIFSAverages values whose rows match every condition.
- MAXIFSThe largest value among rows that match every condition.