Google Sheets AVERAGEIFS Function: Syntax, Examples & Tips

What Is AVERAGEIFS?

AVERAGEIFS calculates the mean of values that satisfy all of several conditions. It extends AVERAGEIF the same way SUMIFS extends SUMIF. Typical questions: "What was the average order value in the West region during Q3?" or "What is the average score for Grade 10 students who passed?"

How AVERAGEIFS Works

The first argument is the range you want to average. After it come one or more pairs of a criteria range and a criterion. For each row, AVERAGEIFS checks every condition. Only rows that pass all of them (AND logic) are included. Text and blank cells in average_range are ignored.

Every criteria range must be the same size and shape as average_range.

Basic Examples

Two text conditions

=AVERAGEIFS(D2:D500, A2:A500, "West", B2:B500, "Enterprise")

Averages column D for rows where the region is "West" and the segment is "Enterprise".

A numeric range

=AVERAGEIFS(C2:C500, C2:C500, ">=100", C2:C500, "<=500")

Averages values between 100 and 500 inclusive. The same range can appear as both the averaged range and a criteria range.

A date window

=AVERAGEIFS(C2:C500, A2:A500, ">="&DATE(2026,7,1), A2:A500, "<"&DATE(2026,10,1))

Averages column C for Q3 2026.

Criteria from cells

=AVERAGEIFS($D$2:$D$500, $A$2:$A$500, G2, $B$2:$B$500, H$1)

Uses a region in column G and a segment in row 1 to fill a summary grid of averages.

Operators and Wildcards

AVERAGEIFS uses the same criterion syntax as AVERAGEIF:

• Comparison operators in quotes: ">10", "<>Cancelled"
• Joined to a cell value with &: ">"&F1
• Wildcards for text: "*widget*" (contains), "A?" (two characters starting with A)
• "<>" on its own matches non-empty cells, and "" matches empty ones

OR Logic

AVERAGEIFS cannot express "region is West or East" within one criterion. Instead, calculate a combined average from sums and counts:

=(SUMIFS(D:D, A:A, "West") + SUMIFS(D:D, A:A, "East")) / (COUNTIFS(A:A, "West", D:D, "<>") + COUNTIFS(A:A, "East", D:D, "<>"))

Or use AVERAGE with FILTER for more flexible conditions.

AVERAGEIF vs. AVERAGEIFS

Common Use Cases

• Sales analysis: average deal value by region, rep, and quarter
• HR: average salary by department and level
• Marketing: average CPC by channel and month
• Operations: average handling time by team and priority
• Education: average marks by class and term

Common Errors

• #DIV/0!: No row met every condition. Use =IFERROR(AVERAGEIFS(...), "") to display a blank.
• #VALUE! from size mismatch: All ranges must have the same dimensions. D2:D500 with A2:A499 fails.
• Argument order: Putting the averaged range last (AVERAGEIF style) returns wrong results or errors. It always comes first in AVERAGEIFS.
• Unquoted operators: Write ">=100", not >=100.

Tips and Best Practices

• Lock ranges with $ before copying a summary formula across a grid.
• Use whole columns (A:A) only when the sheet has no stray values below the data.
• Check how many rows contributed with COUNTIFS using the same conditions.
• Text criteria are not case-sensitive.

Related Functions

• AVERAGEIF: single-condition average
• AVERAGE: unconditional mean
• SUMIF: conditional sum
• IF: custom logic for edge cases