Quick Answer

AVERAGEIFS Function

Averages values that meet all of the specified conditions.

Syntax

AVERAGEIFS

(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Parameters

Parameter	Description	Required
average_range	The range of cells to average.	Required
criteria_range1	The range to evaluate with the first criteria.	Required
criteria1	The first condition.	Required
criteria_range2, criteria2	Additional range/criteria pairs (optional).	Optional

Basic Example

Average sales in the East region

=AVERAGEIFS(B2:B100, A2:A100, "East")

ResultAverage East sales

Averages B2:B100 where A2:A100 equals "East".

Advanced Examples

Example 1: Multiple criteria

Regional product performance

Average Widget sales in the East region

=AVERAGEIFS(C2:C100, A2:A100, "East", B2:B100, "Widget")

Result: Average Widget sales in East

Both conditions must be met.

How AVERAGEIFS Works

AVERAGEIFS evaluates each criteria pair with AND logic. Rows that satisfy all conditions have their corresponding value in average_range included in the average.

1

Select average range

Choose the cells to average.

2

Add criteria pairs

Add each range/criteria pair.

3

Press Enter

Result appears after all conditions are applied.

Important Notes & Limitations

All criteria ranges must match average_range in size.
Only AND logic is supported.
If no cells match, returns #DIV/0!.

Common Errors & Fixes

#DIV/0! errorNo cells match all criteria.

Fix: Loosen criteria or check data.

#VALUE! errorCriteria ranges have different dimensions.

Fix: Make ranges the same size.

Download Practice File

Practice AVERAGEIFS with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the AVERAGEIFS function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

AVERAGE

Calculates the mean of the provided numbers.

AVERAGEIF

Averages values that satisfy one condition.

CORREL

Pearson correlation coefficient between two arrays.

COUNT

COUNT returns the count of cells containing numbers in a range.

COUNTA

COUNTA returns the count of all non-empty cells in a range, including text, numbers, and logical values.

COUNTBLANK

COUNTBLANK returns the count of completely blank cells in a range.

Frequently Asked Questions

Can AVERAGEIFS use OR logic?

No, it uses AND logic. For OR, add multiple AVERAGEIFS results.

What is the maximum number of criteria pairs?

Excel supports up to 127 range/criteria pairs.

Can criteria be cell references?

Yes, use the cell reference directly without quotes.