AVERAGE & AVERAGEIF in Google Sheets
Use AVERAGE for the mean of a range, and AVERAGEIF or AVERAGEIFS when you only want the mean of rows that meet a condition.
=AVERAGE(A2:A100)
Returns the arithmetic mean of the numbers in A2:A100. Blank and text cells are ignored automatically.
How it works
AVERAGE adds the numbers in a range and divides by how many numbers there are; it skips blanks and text. AVERAGEIF averages only the rows that meet one condition, and AVERAGEIFS handles several conditions — its average range comes first, like SUMIFS.
Variations
Average only rows matching a label
=AVERAGEIF(A2:A100, "West", C2:C100)
Mean of column C for rows where A equals West.
Average of values over a threshold
=AVERAGEIF(C2:C100, ">0")
Ignores zeros and negatives; with no third argument it averages the tested range.
Average with multiple conditions
=AVERAGEIFS(C:C, A:A, "West", B:B, "Paid")
Mean of C where A is West AND B is Paid.
Examples
| Scenario | Formula |
|---|---|
| Average excluding blanks | =AVERAGE(A2:A100) |
| Average ignoring zeros | =AVERAGEIF(A2:A100, "<>0") |
| Weighted average | =SUMPRODUCT(A2:A10, B2:B10)/SUM(B2:B10) |
FAQ
Does AVERAGE count blank cells as zero?
No. AVERAGE ignores truly blank cells and text. But a cell containing 0 is counted — use AVERAGEIF(range, "<>0") to skip zeros.
How do I average only some rows?
Use AVERAGEIF for one condition or AVERAGEIFS for several: =AVERAGEIF(A:A, "West", C:C).
Why does AVERAGE return #DIV/0!?
There are no numeric values in the range. Wrap it in IFERROR to show a blank or message when the range is empty.