📊 Statistical Function · beginner
AVERAGEIF Function in Google Sheets
Returns the average of values in a range that meet a single condition. The conditional version of AVERAGE.
Syntax
AVERAGEIF(criteria_range, criterion, [average_range])Returns: The mean of values in average_range where criteria_range matches criterion.
Excel equivalent: AVERAGEIF (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| criteria_range | Required | The range to test against criterion. |
| criterion | Required | The condition to match. A value, text, or comparison. |
| average_range | Optional | The range whose values are averaged when criterion matches. Defaults to criteria_range. |
Examples
Average sale by product
=AVERAGEIF(A2:A100, "Apple", B2:B100)Mean of column B values where column A is "Apple".
Values above a threshold
=AVERAGEIF(B2:B100, ">1000")Mean of values in B greater than 1000.
Wildcard match
=AVERAGEIF(A:A, "*Pro*", B:B)Average of B where A contains "Pro" anywhere.
When to use an alternative
- AVERAGEIFS — You have two or more conditions.
- QUERY with AVG and GROUP BY — Averages across many categories at once.
Common errors and how to fix them
#DIV/0!
Cause: No rows match criterion.
Fix: Test with COUNTIF first. Wrap with IFERROR.
Zeros included
Cause: Cells with 0 count as values.
Fix: Use AVERAGEIFS with B:B, "<>0".
Related functions
AVERAGEIFS
Returns the average of values that meet multiple conditions....
AVERAGE
Returns the arithmetic mean of numeric values in a range. Ignores text and blanks but treats boolean...
SUMIF
Adds the values in a range that meet a single condition. The conditional version of SUM, used for ca...
COUNTIF
Counts the cells in a range that meet a single condition. The most-used way to answer "how many of X...
QUERY
Runs a SQL-like query against a range. Google Sheets' most powerful function for filtering, sorting,...
Frequently Asked Questions
Why #DIV/0!?
No rows matched. Test the criterion with COUNTIF first.
Date conditions?
Yes: AVERAGEIF(A:A, ">="&DATE(2026,1,1), B:B). & concatenates operator with date.