ExcelFlux

AVERAGEIFS function

the average (arithmetic mean) of all cells that meet multiple criteria.

Statistical

Where can you use it?

Analytics, quality and research teams. Use this group to examine distributions, variability or relationships in measurements, sales or service durations.

Scenario in the workbook

Average paid invoices by region. Use IFERROR to display an explanation if no records match.

A worked example from the workbook

RowABC
6RegionStatusAmount
7NorthPaid120
8SouthPaid75
9NorthOpen40
10NorthPaid80
11SouthOpen0
North / Paid
=AVERAGEIFS(C7:C11,A7:A11,"North",B7:B11,"Paid")

Expected result100

See the inputs and instructions for all 3 examples

Syntax

AVERAGEIFS(average_range, criteria_range1, criteria1, …)

Copyable formulas

English Excel · en-US
=AVERAGEIFS(C7:C11,A7:A11,"North",B7:B11,"Paid")
Turkish Excel · tr-TR
=ÇOKEĞERORTALAMA(C7:C11;A7:A11;"North";B7:B11;"Paid")

Worked examples

Average paid invoices by region. Use IFERROR to display an explanation if no records match.

Open the English sheet. Inputs begin at A6. F7:F9 show the live results and G7:G9 show the original results for comparison.

Row A B C
6 Region Status Amount
7 North Paid 120
8 South Paid 75
9 North Open 40
10 North Paid 80
11 South Open 0
Case Formula Expected result
North / Paid =AVERAGEIFS(C7:C11,A7:A11,"North",B7:B11,"Paid") 100
South / Paid =AVERAGEIFS(C7:C11,A7:A11,"South",B7:B11,"Paid") 75
West · error handling =IFERROR(AVERAGEIFS(C7:C11,A7:A11,"West",B7:B11,"Paid"),"No rows") No rows
Microsoft Excel reference