AVERAGEIFS in Excel: Averages With Several Conditions

AVERAGEIFS in Excel works out an average using only the rows that meet several conditions at once, such as "orders in the North and sent by Swift". It is the averaging partner of SUMIFS and COUNTIFS, with the same argument pattern. This guide covers the syntax, examples with text, numbers and dates, and the #DIV/0! error you'll see when nothing matches.

What does AVERAGEIFS do?#

AVERAGEIFS finds the average (the arithmetic mean) of the numbers in one range, counting only the rows where every condition is TRUE. Its syntax is:

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

AVERAGEIFS works in Excel 2007 and every later version, including Microsoft 365 and Excel for the web. You can add up to 127 range and criteria pairs. The AVERAGEIFS function guide summarises the arguments.

Every example uses this delivery log in A1:C8:

How to use AVERAGEIFS step by step#

To find the average delivery time for Swift orders in the North:

1. Click an empty cell, such as F2.
2. Type =AVERAGEIFS(C2:C8, to set the numbers to average.
3. Type A2:A8,"North", for the first condition.
4. Type B2:B8,"Swift") for the second condition.
5. Press Enter.

=AVERAGEIFS(C2:C8,A2:A8,"North",B2:B8,"Swift")

The result is 2.5. Only rows 2 and 5 are North and Swift, with 2 and 3 days, and (2 + 3) / 2 = 2.5. All the ranges must be the same size, or AVERAGEIFS returns #VALUE!.

AVERAGEIFS examples with text, numbers and dates#

The criteria follow the same rules as COUNTIFS and SUMIFS. Text and operators go in double quotes; numbers and cell references don't. Here are more questions on the delivery log:

Notice that a criteria range can be the same as the average range, as in the last two number examples. "Days from 3 to 6" averages 4, 5, 3, 6 and 3, which is 21 / 5 = 4.2.

For dates, join the operator to DATE with &. If column D held order dates, =AVERAGEIFS(C2:C8,D2:D8,">="&DATE(2024,7,1)) would average the days for orders from 1 July 2024 onwards. To use a value from a cell, join it the same way: ">="&H1, never ">=H1".

AVERAGEIF vs AVERAGEIFS#

AVERAGEIF handles one condition; AVERAGEIFS handles one or more. The big trap is the argument order:

=AVERAGEIF(A2:A8,"North",C2:C8)
=AVERAGEIFS(C2:C8,A2:A8,"North")

Both return 4.25. AVERAGEIF puts the average range last, and AVERAGEIFS puts it first. Many people use AVERAGEIFS for everything so they only remember one order. The guide to AVERAGEIF covers the single-condition version, and the comparison of AVERAGEIF vs AVERAGEIFS goes deeper.

How to fix #DIV/0! in AVERAGEIFS#

AVERAGEIFS returns #DIV/0! when no row meets every condition, because an average of nothing means dividing by zero. For example, =AVERAGEIFS(C2:C8,A2:A8,"West") has no West rows to average.

If an empty result is expected, wrap the formula in IFERROR to show a message instead:

=IFERROR(AVERAGEIFS(C2:C8,A2:A8,"West"),"No data")

If you expected matches, don't hide the error. Check the spelling of the criteria, look for extra spaces in the data, and make sure the numbers in the average range are real numbers, not text.

How blanks, text and zeros are treated#

AVERAGEIFS ignores blank cells and text in the average range, so a missing value doesn't drag the average down. Zeros are different: a 0 is a real number, so it is included. If a 0 means "no data" in your sheet, add a condition that leaves zeros out:

=AVERAGEIFS(C2:C8,A2:A8,"North",C2:C8,"<>0")

The guide to averaging without zeros covers this case in more detail.

One more check: if the whole average range is blank or text, there are no numbers to average, so you get #DIV/0! even when the conditions match.

Related functions: MAXIFS, MINIFS and more#

AVERAGEIFS belongs to a family of functions with the same pattern:

• SUMIFS and COUNTIFS add and count with several conditions.
• MAXIFS and MINIFS find the largest and smallest value with several conditions. They need Excel 2019 or later. See MAXIFS and MINIFS.

Once you know one, you know them all; only the function name and the first range change. These are core tools for reporting roles, as the guide to Excel skills for operations managers explains.

How to build an average table by region and courier#

AVERAGEIFS is ideal for a small grid with one condition down the side and another across the top. Put the regions North, South and East in E2:E4, and the couriers Swift and Parcel in F1:G1. In F2, type:

=IFERROR(AVERAGEIFS($C$2:$C$8,$A$2:$A$8,$E2,$B$2:$B$8,F$1),"-")

Fill it right to G2, then down to row 4. The result:

The $ signs do the work. $E2 keeps the column fixed so every cell reads the region from column E, and F$1 keeps the row fixed so every cell reads the courier from row 1. East has no Parcel orders, so IFERROR shows a dash instead of #DIV/0!.

Practise it#

AVERAGEIFS is quick to learn once the argument order sticks. Practise AVERAGEIF, MAXIFS and related functions in a live spreadsheet: you type each formula and the lesson checks it instantly. Or work out the North and Swift average in the exercise on this page first.