If you want to average only the numbers that meet more than one condition, a plain AVERAGE won’t get you there. It averages everything in the range, with no way to say “only these rows.”
That’s exactly what the AVERAGEIFS function is for. You give it a range of numbers to average, then pair up as many criteria as you need, and it averages only the rows where every condition is true.
In this article, I’ll show you how to use AVERAGEIFS with two or more criteria, comparison operators, date ranges, and wildcards, and how to handle the #DIV/0! error when nothing matches.
AVERAGEIFS Syntax and Arguments
Here is the syntax of the AVERAGEIFS function:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- average_range – the range of numbers you actually want to average.
- criteria_range1 – the first range Excel checks a condition against.
- criteria1 – the condition applied to criteria_range1 (a number, text, or an expression like “>100”).
- [criteria_range2, criteria2] – optional extra range and condition pairs. You can add up to 127 pairs.
The one thing to burn into memory: average_range comes first, before any criteria. This trips people up because AVERAGEIF (the single-criterion version) puts the average range last. AVERAGEIFS flips it to the front, the same way SUMIFS does.
Every criteria_range has to be the same size and shape as average_range. If average_range is 12 rows, each criteria_range needs to be the same 12 rows.
Pro Tip: Text criteria and any criteria using an operator (like “>100” or “<>Laptop”) must be wrapped in double quotes. Plain numbers like 100 don’t need quotes.
Example 1: Average With Two or More Criteria
Let’s start with the most common use of AVERAGEIFS: averaging with two conditions at once.
Below is a sales dataset. Column A has the rep, column B the region, column C the product, column D units sold, column E the sale amount, and column F the date. I want the average sale amount for laptops sold in the East region.
Here is the formula:
=AVERAGEIFS(E2:E13, B2:B13, "East", C2:C13, "Laptop")
This returns 6700. Excel looks down the region column for “East” and the product column for “Laptop”, finds the two rows where both are true, and averages their amounts (9000 and 4400).
The pattern is always the same. The range you’re averaging goes first, then each condition is a range/criteria pair. Add a third pair and Excel narrows it further, keeping only rows where all three are true. It’s the same multiple-criteria logic COUNTIFS and SUMIFS use.
Example 2: Average Using Comparison Operators (Greater Than, Less Than)
Now let’s use a criterion that isn’t an exact match. Comparison operators like >, <, >=, and <> let you average based on a threshold instead of a fixed value.
Same sales dataset as before. This time I want the average sale amount for orders of at least 10 units, using the units column in D.
Here is the formula:
=AVERAGEIFS(E2:E13, D2:D13, ">=10")
This returns 5350, the average amount across the eight rows where units are 10 or more. Note the whole condition ">=10" sits inside double quotes, operator and number together.
If your threshold lives in a cell instead of being typed in, join the operator to the cell reference with an ampersand. So ">="&H1 reads the number from H1. Only the operator goes in quotes here, not the cell reference.
Example 3: Average Between Two Dates
A common task is averaging values that fall inside a date window. You do this with two criteria on the same date column: one for the start date and one for the end date.
Using the same dataset, I want the average sale amount for orders placed in February 2026, between the 1st and the 28th. The dates are in column F.
Here is the formula:
=AVERAGEIFS(E2:E13, F2:F13, ">="&DATE(2026,2,1), F2:F13, "<="&DATE(2026,2,28))
This returns 4950, the average of the four February orders. The same date column F2:F13 is used twice, once with a “on or after the 1st” condition and once with a “on or before the 28th” condition.
I’m using DATE(2026,2,1) instead of typing “2/1/2026” so Excel reads it as a real date no matter what regional format your machine uses. It’s the safer way to feed a date into a criterion.
Example 4: Average Using Wildcards (Partial Text Match)
AVERAGEIFS accepts wildcard characters in text criteria, so you can match on part of a word instead of the whole thing. The asterisk * stands for any number of characters, and the question mark ? stands for exactly one character.
Same dataset again. Say the product column has entries like “Laptop” and “Laptop Pro”, and I want every product that starts with “Lap” sold in the West region. I’ll match on "Lap*".
Here is the formula:
=AVERAGEIFS(E2:E13, B2:B13, "West", C2:C13, "Lap*")
This returns 8000, the average amount of the three West-region rows whose product starts with “Lap”. The * after “Lap” tells Excel to accept anything that follows.
If you ever need to match a literal asterisk or question mark, put a tilde in front of it (~* or ~?) so Excel treats it as a real character instead of a wildcard.
Example 5: Handling the #DIV/0! Error When Nothing Matches
Here’s the gotcha that catches people. When no rows meet all your criteria, AVERAGEIFS has nothing to average, so it returns a #DIV/0! error rather than a blank or a zero.
Using the same sales data, suppose I ask for the average amount in the “South” region. There are no South rows in the data at all.
Here is the formula:
=AVERAGEIFS(E2:E13, B2:B13, "South")
Because nothing matches, this returns #DIV/0! instead of a number. To show a friendly message instead of the error, wrap the whole thing in IFERROR.
Here is the formula:
=IFERROR(AVERAGEIFS(E2:E13, B2:B13, "South"), "No matching sales")
Now the cell shows “No matching sales” whenever the criteria come up empty, which is much easier to read on a dashboard than a raw error.
Pro Tip: AVERAGEIFS also throws #DIV/0! if the average_range itself holds text or blanks in the matched rows, since those can’t be averaged. Make sure average_range points at numbers.
Example 6: Get an Average Per Category in One Formula (Excel 365)
AVERAGEIFS returns a single value, so all the examples above land in one cell. In Excel 365, there’s a neat way to get an average for every category at once by pairing it with the UNIQUE function.
Using the same sales dataset, I want the average sale amount for each region, without typing a separate formula per region.
Here is the formula:
=AVERAGEIFS(E2:E13, B2:B13, UNIQUE(B2:B13))
UNIQUE pulls out the distinct regions (East, West, North), and AVERAGEIFS returns one average for each. The result spills down automatically, so East shows 4600, West 6750, and North about 5467. No dragging, no copying the formula down.
This only works in Excel 365 and Excel for the web, where dynamic arrays are supported. In Excel 2019 or 2021, you’d write a separate AVERAGEIFS for each region instead.
AVERAGEIF vs AVERAGEIFS
These two look almost identical, and the extra “S” is easy to miss. The difference is simple:
- AVERAGEIF handles a single condition. Its order is
=AVERAGEIF(range, criteria, [average_range]), with the range you average last (and optional). - AVERAGEIFS handles one or more conditions. Its order is
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...), with the range you average first.
If you only ever have one condition, either one works. The moment you need a second condition, reach for AVERAGEIFS.
My habit is to just use AVERAGEIFS everywhere. The argument order is always the same, so I never have to remember which version I’m in.
Things to Keep in Mind
A few points that save headaches when working with AVERAGEIFS:
- average_range goes first. Unlike AVERAGEIF, the range you’re averaging is the first argument, not the last.
- Ranges must match in size. average_range and every criteria_range must cover the same number of rows, or Excel returns a #VALUE! error.
- Quote your operators and text. Conditions like “>100”, “<>East”, or “Laptop” go in double quotes. Bare numbers don’t.
- Empty cells count as zero in criteria ranges. A blank cell in a criteria range is read as 0, which can pull in rows you didn’t expect.
- Every criterion must be true. AVERAGEIFS uses AND logic. A row is included only when it satisfies all the conditions at once.
That covers the main ways to use AVERAGEIFS in Excel, from two simple criteria to date ranges, wildcards, and a modern per-category spill. Once you’re comfortable with the average_range-first order, it works just like SUMIFS and COUNTIFS. I hope you found this tutorial helpful.
Other Excel Articles You May Also Like:
