Google Sheets AVERAGEIF Function: Syntax, Examples & Tips

What Is AVERAGEIF?

AVERAGEIF returns the mean of the values that meet one condition. It works like SUMIF, except that it divides the matching total by the number of matches. Use it when you want an answer such as "average order value for the West region" or "average score above 50".

How AVERAGEIF Works

AVERAGEIF checks every cell in criteria_range against the criterion. For each match, the cell in the same position in average_range is included in the average. If you leave out average_range, AVERAGEIF averages the matching cells in criteria_range itself. Text and blank cells in the averaged range are ignored, just as with AVERAGE.

Basic Examples

Average by category

=AVERAGEIF(A2:A200, "West", C2:C200)

Averages column C for every row where column A is "West".

Average values above a threshold

=AVERAGEIF(B2:B100, ">50")

Averages only the numbers in B2:B100 that are greater than 50.

Exclude zeros

=AVERAGEIF(B2:B100, "<>0")

Averages every non-zero number, a common way to ignore placeholder zeros.

Use a cell as the criterion

=AVERAGEIF(A2:A200, F1, C2:C200)

Lets you change the category in F1 and see the result update.

Comparison Operators

Combine an operator with a cell value using &:

=AVERAGEIF(B2:B100, ">="&E1)

Wildcards

For text criteria, * matches any run of characters and ? matches exactly one:

=AVERAGEIF(A2:A200, "*Pro*", C2:C200)

Averages column C for any product name that contains "Pro". To match a literal asterisk or question mark, put a tilde in front (~*).

Dates as Criteria

=AVERAGEIF(A2:A365, ">="&DATE(2026,7,1), B2:B365)

Averages values for dates on or after 1 July 2026. Build date criteria with DATE rather than typing a date string, to avoid locale problems.

Common Use Cases

• Sales: average deal size per rep or region
• Support: average resolution time for one priority level
• Education: average score for a class or section
• Marketing: average cost per click for one channel
• Data cleaning: averages that skip zeros or a "test" category

Common Errors

• #DIV/0!: No cells matched the criterion, or the matching cells contain no numbers. Wrap with IFERROR to show a blank: =IFERROR(AVERAGEIF(...), "").
• Operator outside quotes: >50 must be written as ">50".
• Mismatched ranges: Keep criteria_range and average_range the same size and aligned on the same rows.
• Numbers stored as text: These are skipped in the averaged range and can produce a misleading average.

Tips and Best Practices

• Text matching is not case-sensitive: "west" matches "West".
• For two or more conditions, switch to AVERAGEIFS, which puts the averaged range first.
• To count how many rows the average is based on, pair it with COUNTIF using the same criterion.

Related Functions

• AVERAGEIFS: averages with multiple conditions
• AVERAGE: unconditional mean
• SUMIF: sums with a condition
• IF: build more complex logic