Conditional formula page

AVERAGEIFS Formula

Use AVERAGEIFS when all criteria must match before a number is included in the average.

Best for

Average values by multiple conditions with AVERAGEIFS.

What it returns

With the sample data, this averages East Widget amounts.

Copy formulas

Excel formula

=AVERAGEIFS(D2:D100, B2:B100, "East", C2:C100, "Widget")

Google Sheets formula

=AVERAGEIFS(D2:D100, B2:B100, "East", C2:C100, "Widget")

Excel / Google Sheets difference

Excel and Google Sheets use the same AVERAGEIFS argument order. Every criteria pair must match the same row before its numeric average value is included.

Example data

Date	Region	Product	Amount	Rep
2026-01-04	East	Widget	420	Maya
2026-01-12	West	Widget	310	Noah
2026-02-03	East	Gadget	275	Maya
2026-02-15	East	Widget	640	Iris

What it returns

With the sample data, this averages East Widget amounts.

How the formula works

D2:D100 is the average range.
B2:B100 must equal East.
C2:C100 must equal Widget.

Syntax piece	Role in the formula
D2:D100	The numeric range to average.
B2:B100, "East"	The region criteria pair.
C2:C100, "Widget"	The product criteria pair.

Verified examples

East Widget average

=AVERAGEIFS(D2:D100, B2:B100, "East", C2:C100, "Widget")

Excel: Use the sample sales table with Region, Product, and Amount columns. Returns: 530

East-only average

=AVERAGEIFS(D2:D100,B2:B100,"East")

Google Sheets: Use the sample sales table. Returns: 445

February East average

=AVERAGEIFS(D2:D100,B2:B100,"East",A2:A100,">="&DATE(2026,2,1),A2:A100,"<"&DATE(2026,3,1))

Excel: Use the sample dates and include February 1 through before March 1. Returns: 457.5

Common errors and fixes

Issue	Likely cause	Fix
AVERAGEIFS returns #DIV/0!	No row satisfies all criteria with a numeric value in the average range.	Check each criterion and decide whether a no-match state should be wrapped in IFERROR.
The average uses the wrong rows	The average and criteria ranges have different row boundaries.	Align every range, such as D2:D100, B2:B100, and C2:C100.
Numeric-looking values are ignored	The matching amount cells are stored as text.	Convert imported amounts to numbers before averaging.

When not to use this formula

Do not use AVERAGEIFS when you need the matching records themselves; use FILTER or QUERY instead.

Alternatives

Alternative	When to use it
AVERAGEIF Formula	Use when only one condition controls the average.
SUMIFS Formula	Use when matching values should be totaled instead of averaged.

Related formulas

Official references

AVERAGEIFS function from Microsoft

FAQ

Does AVERAGEIFS use AND logic?

Yes. Every criteria pair must match the same row before its value is averaged.

Are blanks, text, and zeros treated the same?

Blanks and text are ignored in the average range, while numeric zeros are included.