AVERAGEIF Function in Google Sheets: Complete Guide

Key Insights

• AVERAGEIF calculates the average of cells that meet a single criterion, using the syntax =AVERAGEIF(range, criterion, [average_range]) where the third argument is optional when averaging the same range you’re evaluating
• Text criteria are case-insensitive by default, and wildcards (* for multiple characters, ? for single characters) enable powerful partial matching for flexible data analysis
• When your criteria range differs from your values range, both ranges must be the same size, or you’ll get incorrect results without any error message—a silent bug that’s easy to miss

Introduction to AVERAGEIF

AVERAGEIF is one of the most practical functions in Google Sheets for conditional calculations. It calculates the average of cells that meet a specific criterion, filtering out irrelevant data automatically.

The syntax is straightforward:

=AVERAGEIF(range, criterion, [average_range])

• range: The cells to evaluate against your criterion
• criterion: The condition that determines which cells to include
• average_range: (Optional) The actual cells to average. If omitted, the function averages the cells in range

Understanding when to use AVERAGEIF versus its relatives matters:

• AVERAGE: Use when you want the average of all values, no filtering needed
• AVERAGEIF: Use when you have one condition to filter by
• AVERAGEIFS: Use when you need multiple conditions (AND logic)

If you’re calculating the average sale amount for a specific region, AVERAGEIF is your tool. If you need the average sale amount for a specific region during a specific quarter, upgrade to AVERAGEIFS.

Basic Usage with Numeric Criteria

Numeric criteria are the most common use case. You’ll use comparison operators wrapped in quotes to define your conditions.

Here’s a dataset of sales transactions:

Average of sales above $500:

=AVERAGEIF(B2:B6, ">500")

Result: 720 (averages 720, 550, and 890)

Average of sales at or below $500:

=AVERAGEIF(B2:B6, "<=500")

Result: 415 (averages 450 and 380)

Average of sales not equal to a specific value:

=AVERAGEIF(B2:B6, "<>550")

Result: 610 (excludes 550, averages the rest)

The comparison operators available are:

• > greater than
• < less than
• >= greater than or equal to
• <= less than or equal to
• <> not equal to
• = equal to (or just the value itself)

For exact numeric matches, you can skip the operator:

=AVERAGEIF(B2:B6, 550)

This returns 550 since only one cell matches.

Practical example—average test scores below passing:

=AVERAGEIF(C2:C50, "<60")

This instantly tells you the average score among failing students, useful for identifying how much remediation is needed.

Text-Based Criteria

Text criteria let you filter by categories, names, or any string values. By default, text matching is case-insensitive.

Using the same sales data, to get the average sale amount for Alice:

=AVERAGEIF(A2:A6, "Alice", B2:B6)

Result: 415 (averages 450 and 380)

Notice we now use the third argument. The first range (A2:A6) contains the names we’re checking. The third range (B2:B6) contains the values we’re averaging.

Wildcards for partial matching:

The asterisk (*) matches any sequence of characters. The question mark (?) matches exactly one character.

Average revenue for all “Pro” products:

=AVERAGEIF(A2:A6, "Pro*", B2:B6)

Result: 2700 (averages 1200, 2400, and 4500)

Average revenue for products ending in “Basic”:

=AVERAGEIF(A2:A6, "*Basic", B2:B6)

Result: 900 (averages 1200 and 600)

Matching a specific pattern with single-character wildcard:

If you have product codes like “A1”, “A2”, “B1”, “B2”:

=AVERAGEIF(A2:A10, "A?", B2:B10)

This matches “A1”, “A2”, etc., but not “A10” (two characters after A).

Escaping wildcards:

If your data actually contains asterisks or question marks, prefix them with a tilde (~):

=AVERAGEIF(A2:A10, "Special~*", B2:B10)

This matches the literal text “Special*”.

Working with Separate Criteria and Average Ranges

The optional third argument is where AVERAGEIF becomes powerful for real-world data analysis. Your evaluation criteria often live in a different column than the values you want to average.

Average salary by department:

=AVERAGEIF(A2:A6, "Engineering", C2:C6)

Result: 91500 (averages 95000 and 88000)

=AVERAGEIF(A2:A6, "Sales", C2:C6)

Result: 75000 (averages 72000 and 78000)

Critical pitfall—mismatched range sizes:

This is a silent killer. If your criteria range and average range have different sizes, Google Sheets doesn’t throw an error. It assumes the average range starts at the same position as the criteria range and has the same dimensions.

// WRONG - ranges are different sizes
=AVERAGEIF(A2:A6, "Engineering", C2:C10)

// CORRECT - ranges match
=AVERAGEIF(A2:A6, "Engineering", C2:C6)

The first formula won’t error, but it will give you incorrect results. Always double-check that your ranges have the same number of rows (or columns, for horizontal data).

Dynamic Criteria with Cell References

Hardcoding criteria works for one-off calculations, but real spreadsheets need flexibility. Reference cells instead of typing values directly.

Basic cell reference:

If cell F1 contains “Engineering”:

=AVERAGEIF(A2:A6, F1, C2:C6)

Now changing F1 updates the calculation instantly.

Combining operators with cell references:

This is where people get tripped up. You can’t just write >=F1. You need to concatenate the operator with the cell value:

=AVERAGEIF(B2:B6, ">="&F1)

If F1 contains 500, this evaluates as ">=500".

Building a dashboard with dropdown-driven averages:

Create a data validation dropdown in cell G1 with department names. Then use:

=AVERAGEIF(A2:A100, G1, C2:C100)

Users can now select any department and see the average salary update automatically.

Dynamic threshold example:

| G1: Threshold | 500 | | G2: Average Above Threshold | =AVERAGEIF(B2:B100, “>"&G1) |

Change G1 to any number, and G2 recalculates. This pattern is essential for interactive reports.

Common Errors and Troubleshooting

#DIV/0! Error:

This occurs when no cells match your criterion. AVERAGEIF tries to average zero values, which means dividing by zero.

Fix it with IFERROR:

=IFERROR(AVERAGEIF(A2:A6, "Nonexistent", B2:B6), 0)

Or provide a more descriptive fallback:

=IFERROR(AVERAGEIF(A2:A6, "Nonexistent", B2:B6), "No matches")

Text matching not working:

Check for invisible characters. Data imported from external sources often contains leading/trailing spaces. Use TRIM in your source data or adjust your criterion:

=AVERAGEIF(A2:A6, "*Engineering*", C2:C6)

The wildcards accommodate extra characters.

Empty cells vs. zero values:

AVERAGEIF ignores empty cells but includes zeros. If your data uses zero to represent “no data,” this skews your average.

Filter out zeros explicitly:

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

Or combine with AVERAGEIFS for more control.

AVERAGEIF vs AVERAGEIFS: When to Upgrade

AVERAGEIF handles one condition. When you need multiple conditions evaluated together (AND logic), switch to AVERAGEIFS.

Single condition with AVERAGEIF:

Average salary in Engineering:

=AVERAGEIF(A2:A6, "Engineering", C2:C6)

Multiple conditions with AVERAGEIFS:

Average salary in Engineering where salary exceeds 90000:

=AVERAGEIFS(C2:C6, A2:A6, "Engineering", C2:C6, ">90000")

Notice the syntax difference: AVERAGEIFS puts the average range first, then pairs of criteria ranges and criteria.

Same problem, both approaches:

You want the average of sales above $500 for the “North” region.

AVERAGEIF can’t do this directly. You’d need a helper column or array formula.

AVERAGEIFS handles it cleanly:

=AVERAGEIFS(B2:B100, B2:B100, ">500", C2:C100, "North")

The rule is simple: one condition means AVERAGEIF, multiple conditions means AVERAGEIFS. Don’t force AVERAGEIF into situations where AVERAGEIFS is the right tool.

AVERAGEIF remains the better choice for simpler scenarios—it’s slightly more readable and requires less typing when you only have one criterion. Master it first, then graduate to AVERAGEIFS when your analysis demands it.