Skip to content

Excel function · Statistics

Excel AVERAGEIFS function

AVERAGEIFS averages the rows that meet all of your conditions together: the average value of cable orders in the West, one rep's average sale in October.

In Turkish Excel: ÇOKEĞERORTALAMA

What it does

It is written like SUMIFS: first the range to average, then pairs of criteria range and criteria. A row is included only if it meets every condition.

For a date range, give the same column twice: ">="&DATE(2025,10,1) and "<"&DATE(2025,11,1). To leave zeros out, you can add the average range itself as a condition: …,D2:D50,"<>0".

If no row meets the conditions, the result is #DIV/0!. With the same conditions, SUMIFS adds and COUNTIFS counts; putting all three side by side is an easy way to see how many rows and which total an average comes from.

Syntax

English Excel=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
Turkish Excel=ÇOKEĞERORTALAMA(ortalama_aralığı; ölçüt_aralığı1; ölçüt1; ...)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
average_rangeRequiredThe cells to average, e.g. the amount column D2:D50.
criteria_range1RequiredThe range tested by the first condition; the same size as average_range.
criteria1RequiredThe first condition: "West", ">=1000", a cell or an expression such as ">="&DATE(2025,10,1). Further conditions follow as range and criteria pairs.

Examples

1Average by region and product

The formula in H2 averages only the rows whose region is West and whose product is Cable.

H2fx=AVERAGEIFS(D2:D8,B2:B8,F2,C2:C8,G2)
Average by region and product: example table
ABCDEFGH
1DateRegionProductAmountRegionProductAverage
228-Sep-2025WestCable4,200.00WestCable6,600.00
32-Oct-2025WestCable12,500.00
46-Oct-2025NorthSocket8,300.00
514-Oct-2025WestSocket4,750.50
621-Oct-2025SouthCable9,900.00
730-Oct-2025WestCable3,100.00
83-Nov-2025NorthCable15,200.00

Three rows meet both conditions; their average is 6,600.00.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2One rep's average sale in October

The date column is given twice as a condition, and the rep is the third condition. The September and November rows and other reps' sales are left out.

F2fx=AVERAGEIFS(C2:C7,A2:A7,">="&DATE(2025,10,1),A2:A7,"<"&DATE(2025,11,1),B2:B7,E2)
One rep's average sale in October: example table
ABCDEF
1DateRepAmountRepOctober Average
229-Sep-2025Emma9,800.00Emma10,166.67
33-Oct-2025Emma12,500.00
48-Oct-2025Liam8,300.00
516-Oct-2025Emma7,400.00
627-Oct-2025Emma10,600.00
74-Nov-2025Emma15,200.00

Emma's three October sales average 10,166.67.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Region × product average table

Regions are in column E and products in F1 and G1. The region is given as $E2 and the product as F$1, so each column's formula uses its own header and each row its own region.

F2fx=AVERAGEIFS($C$2:$C$8,$A$2:$A$8,$E2,$B$2:$B$8,F$1)
Region × product average table: example table
ABCDEFG
1RegionProductAmountRegionCableSocket
2WestCable12,000West10,0004,800
3WestSocket4,800North10,5005,800
4NorthCable9,600
5WestCable8,000
6NorthSocket5,400
7NorthSocket6,200
8NorthCable11,400
  • F2 (filled down to F3)=AVERAGEIFS($C$2:$C$8,$A$2:$A$8,$E2,$B$2:$B$8,F$1)
  • G2 (filled down to G3)=AVERAGEIFS($C$2:$C$8,$A$2:$A$8,$E2,$B$2:$B$8,G$1)

In the West, cable averages 10,000 and sockets 4,800; in the North, cable averages 10,500 and sockets 5,800.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#DIV/0!

Cause: No row meets all the conditions together.

Fix: Check how the conditions are spelled; wrap the formula in IFERROR to show text instead.

#VALUE!

Cause: One of the criteria ranges is not the same size as the average range.

Fix: Start and end every range on the same rows.

Average of the wrong column

Cause: Out of AVERAGEIF habit, the average range was put last.

Fix: In AVERAGEIFS the range to average is always the first argument.

Good to know

  • When building a region × product summary, use mixed references such as $E2 for the row condition and F$1 for the column condition; then the formula can be copied across the whole table.
  • Add a COUNTIFS with the same conditions to show how many rows each average is based on.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Show the average order values by region and product group in a summary table

Convert to table

Bring your order list into the tool and type this. Nexell builds each cell of the summary with conditional average formulas and shows it in the preview first; in the downloaded file, the summary follows any change in the orders.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I average values between two dates with AVERAGEIFS?

Give the date column twice as a condition: =AVERAGEIFS(D2:D50,A2:A50,">="&DATE(2025,10,1),A2:A50,"<"&DATE(2025,11,1)).

What is the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF takes one condition and the average range comes last. AVERAGEIFS takes several conditions and the average range comes first.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free