AVERAGEIF vs AVERAGEIFS: Argument Order Matters

The short answer to AVERAGEIF vs AVERAGEIFS: AVERAGEIF averages numbers that meet one condition, and AVERAGEIFS averages numbers that meet one or more conditions at once. The trap is the argument order: the range you average is last in AVERAGEIF and first in AVERAGEIFS. This comparison is for anyone who reports average delivery times, scores, salaries or order values by group.

AVERAGEIF vs AVERAGEIFS at a glance#

AVERAGEIFS does everything AVERAGEIF does and more, but the average range moves to the front.

How the arguments differ#

Both functions check cells against a condition and average the matching numbers. The difference is where the numbers to average go.

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

• range or criteria_range is the column to check, such as Region.
• criteria is what the cells must match, such as "North", ">=4" or a cell reference.
• average_range is the column of numbers to average.

In AVERAGEIF, the average range is optional. Leave it out and the function averages the cells it checked: =AVERAGEIF(C2:C8,">=4") averages every delivery of 4 days or more. In AVERAGEIFS, the average range is required and always first. The AVERAGEIFS function guide explains each argument.

The same averages solved both ways#

Here is a list of deliveries:

Task 1: one condition (North average)#

Both return 3.75, the average of 2, 4, 3 and 6:

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

Same ranges, same condition, different order.

Task 2: two conditions (North and Fast)#

AVERAGEIF can check only one column. AVERAGEIFS adds a second pair:

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

The result is 2.5: rows 2 and 6 are the only North orders sent Fast, with 2 and 3 days.

Task 3: a condition on the numbers themselves#

To average only the slow deliveries of 4 days or more:

=AVERAGEIF(C2:C8,">=4")

The result is 5, the average of 5, 4 and 6. No average range is needed, because the condition is on the same cells.

Task 4: a range of values#

AVERAGEIFS can check the same column twice. This averages deliveries from 3 to 5 days:

=AVERAGEIFS(C2:C8,C2:C8,">=3",C2:C8,"<=5")

The result is 3.75, the average of 5, 4, 3 and 3.

Task 5: criteria from cells and wildcards#

Typing "North" into every formula makes a summary table hard to update. Put the criteria in cells instead. If E2 holds North and F2 holds Fast:

=AVERAGEIFS($C$2:$C$8,$A$2:$A$8,E2,$B$2:$B$8,F2)

This returns 2.5, the same as Task 2. Because the ranges are locked with dollar signs and the criteria cells are not, you can fill the formula down a list of region and courier pairs and every row updates. For a number condition held in a cell, join the operator to it: ">="&G2. Writing ">=G2" inside the quotes compares against the text "G2" and returns #DIV/0!.

Both functions also accept wildcards in text criteria. An asterisk (*) means any number of characters and a question mark (?) means exactly one. =AVERAGEIF(B2:B8,"F*",C2:C8) averages every courier name that starts with F, which here is just Fast, giving 2.5. Wildcards help when names have suffixes, such as "Fast Express" and "Fast Economy".

When to use AVERAGEIF#

Use AVERAGEIF for a quick average with exactly one condition. It fits:

• One group at a time, such as average sales for one region or one product.
• Conditions on the averaged numbers, such as average order value over 100, with no third argument.
• Excluding zeros: =AVERAGEIF(C2:C8,"<>0"). The guide on averaging without zeros covers this in depth.
• Existing files that already use AVERAGEIF, where one style is easier to maintain.
• Quick checks while exploring data, such as typing =AVERAGEIF(B2:B8,"Slow",C2:C8) into a spare cell to see that slow deliveries average 5 days, before you build a proper summary.

The AVERAGEIF guide has more single-condition examples.

When to use AVERAGEIFS#

Use AVERAGEIFS whenever you have two or more conditions, and consider it your default for one. It fits:

• More than one column, such as region and courier, or team and month.
• A range of values, such as dates within a month or scores between two limits.
• Summary tables that may gain conditions later.
• Spotting mistakes. If one range is shorter than the others, AVERAGEIFS returns #VALUE! instead of a quietly wrong average.
• Date periods, such as the average order value in March: use the date column twice, with ">="&DATE(2026,3,1) and "<="&DATE(2026,3,31). Building dates with DATE avoids problems with regional date formats.

The AVERAGEIFS guide works through multi-condition reports, and MAXIFS and MINIFS use the same argument order for the highest and lowest values.

How to handle #DIV/0! and OR conditions#

Both functions return #DIV/0! when no row matches, because an average of nothing means dividing by zero. =AVERAGEIF(A2:A8,"West",C2:C8) returns #DIV/0! on this data. For a clean report, wrap it:

=IFERROR(AVERAGEIF(A2:A8,"West",C2:C8),"No orders")

AVERAGEIFS only does AND logic. For North or South, you can't add two averages together. Instead, add the totals and divide by the added counts:

=(SUMIFS(C2:C8,A2:A8,"North")+SUMIFS(C2:C8,A2:A8,"South"))/(COUNTIFS(A2:A8,"North")+COUNTIFS(A2:A8,"South"))

The result is 3.67 (22 days over 6 orders). Averaging the two group averages, 3.75 and 3.5, would give 3.63, which is wrong because the groups have different sizes.

Common mistakes with AVERAGEIF and AVERAGEIFS#

1. Putting the average range last in AVERAGEIFS. When you switch from AVERAGEIF, move it to the front.
2. Adding a second condition to AVERAGEIF. It accepts only three arguments. Rewrite the formula with AVERAGEIFS.
3. Leaving the operator outside the quotes. Write ">=4", not >=4. To use a cell, join it: ">="&G2.
4. Expecting zeros to be ignored. Blanks are skipped, zeros are not. Add "<>0" as a condition if zeros mean "no data".
5. Averaging averages. Group averages with different group sizes can't simply be averaged. Use totals divided by counts.
6. Ranges of different sizes. AVERAGEIF quietly resizes its average range from the top-left cell, which can hide an off-by-one error. AVERAGEIFS returns #VALUE!.

Averages by group are a staple of operations reporting; see Excel skills for operations managers for where they fit in a typical week.

Our recommendation#

Learn AVERAGEIF first: check, match, average is an easy order to remember. Then make AVERAGEIFS your default, because it handles one condition or many, its order never changes, and it flags mismatched ranges with an error. Wrap either one in IFERROR for reports where a group might be empty, and use SUMIFS divided by COUNTIFS for OR logic.

In short: AVERAGEIFS by default, AVERAGEIF for quick single-condition averages.

Practise it#

Try the exercise on this page: it needs two conditions, so only AVERAGEIFS can do it. Then practise AVERAGEIF, MAXIFS and their relatives in a live spreadsheet: you type each formula, and it checks your answer instantly.