SheetsdrillAVERAGEIF
AVERAGEIF(criteria_range, criterion, [average_range]) returns the mean of the cells in average_range whose matching cell meets the criterion. AVERAGEIFS(average_range, criteria_range1, criterion1, ...) does the same with several conditions, with the average range first.
Syntax
=AVERAGEIF(criteria_range, criterion, [average_range]) and =AVERAGEIFS(average_range, criteria_range1, criterion1, ...)| Part | What it is |
|---|---|
criteria_range | The cells to test. |
criterion | The condition: "Support", ">0", E1, "<>". |
average_range | Optional in AVERAGEIF: the numbers to average; defaults to criteria_range. |
Examples
=AVERAGEIF(B2:B100, "Support", D2:D100)Result: Average reply time in column D for the Support team
=AVERAGEIF(D2:D100, ">0")Result: The average of the positive values only
=AVERAGEIFS(D:D, B:B, "Support", C:C, "Chat")Result: Average reply time for Support chats
Common mistakes
- #DIV/0! means no cell met the condition; wrap the formula in IFERROR.
- Durations stored as text such as 2h 15m are ignored; store times as real durations or minutes as numbers.
- Zeros are included in the average; exclude them with AVERAGEIFS(D:D, B:B, "Support", D:D, ">0").
Practice AVERAGEIF
These drills check your answer on live data, free, no account needed.
- Average reply timeFind the average minutes to reply for the channel in F1 and the priority in F2. Answer in F3.
Questions
How do I average by date range?
=AVERAGEIFS(D:D, A:A, ">="&E1, A:A, "<="&E2).
How do I average without blanks?
AVERAGE and AVERAGEIF already skip empty cells; they only count cells with numbers.
Related
- SUMIFHow SUMIF works in Google Sheets: add up values where another column matches text, a number, a date or a wildcard, with examples and the mistakes that give 0.
- COUNTIFHow COUNTIF in Google Sheets counts cells equal to a value, above a number, containing text or not blank, with examples, wildcards and the errors that give 0.
- SUM and AVERAGEHow SUM and AVERAGE work in Google Sheets, with examples for a column total, average order value, share of total with locked references, and why you get 0.
- SUMIFSHow SUMIFS in Google Sheets adds values that meet several conditions at once, with examples for a rep and month, date ranges, not-equal and wildcards.