What was the average order value for notebooks sold in the East region? A plain AVERAGE takes everything, which answers the wrong question. AVERAGEIFS averages only the rows that pass your conditions, so you get the average for exactly the group you care about — by region, by product, by month, or all three at once.
What AVERAGEIFS does
AVERAGEIFS works like SUMIFS and COUNTIFS, but it returns an average instead of a sum or a count. You give it the column to average first, then pairs of criteria ranges and rules. A row contributes to the average only when it passes every condition, and blank or text cells in the average column are ignored rather than counted as zero.
The syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- average_range — the column of numbers to average, for example the Amount column.
- criteria_range1 — the first column to test, for example Region.
- criteria1 — the rule, for example “East” or “>100”.
- criteria_range2, criteria2, … — more range-and-rule pairs. A row must pass all of them.
Step-by-step example
Find the average notebook order in the East region from this sales list.
- Click the cell where the answer should go.
- Type
=AVERAGEIFS(D2:D9,A2:A9,"East",B2:B9,"Notebook")— D is the amount to average, A tests the region, B tests the product. - Press Enter. The average of the matching orders appears.
- To see the average for a different group, change the criteria — for example
"West"instead of"East". - For a date window, add a third pair on the date column, for example
C2:C9,">=2026-01-01".
Worked example
| Region | Product | Date | Amount |
|---|---|---|---|
| East | Notebook | 2026-01-05 | $120 |
| East | Pen set | 2026-01-06 | $85 |
| West | Notebook | 2026-01-07 | $200 |
| East | Notebook | 2026-01-12 | $95 |
=AVERAGEIFS(D2:D5,A2:A5,"East",B2:B5,"Notebook") returns $107.50 — the average of $120 and $95. The Pen set and West rows are excluded before the average is calculated, which is why the answer is not the average of all four amounts.
Common errors
#DIV/0! — no rows matched. No row passed all your conditions, so there is nothing to average. Loosen one condition and check the count first with COUNTIFS using the same criteria.
The average looks too low. Text or blank cells in the average range are ignored, but a real 0 is averaged in. A $0 order drags the average down — filter it out with an extra condition such as D2:D9,">0".
Ranges are different sizes. Every criteria range must have the same number of rows as the average range. A range that starts one row lower than the others silently shifts every test.
Dates stored as text. A date condition on a text column matches nothing. Check with =ISNUMBER(C2) — if it says FALSE, the dates need converting with DATEVALUE or Text to Columns.
Tip
If you need an average with one condition only, AVERAGEIF does the same job with a shorter formula. But AVERAGEIFS is the one to learn: the same pattern (range first, then pairs) works for SUMIFS and COUNTIFS too.
Averages with OR conditions
AVERAGEIFS only does AND — every condition must pass. For an OR average, such as the average order for notebooks or pen sets, combine SUMIFS and COUNTIFS instead: =SUM(SUMIFS(D2:D9,B2:B9,"Notebook",B2:B9,"Pen set"))/SUM(COUNTIFS(B2:B9,"Notebook",B2:B9,"Pen set")). The trick is that SUMIFS and COUNTIFS both accept several criteria, so the OR lives in the list of values rather than in the logic. Never average two separate averages unless both groups have the same number of rows — that gives the wrong answer whenever the groups differ in size.
Practice file
Download the AVERAGEIFS practice file (.xlsx) — it contains a sales list and a summary sheet with AVERAGEIFS answers already filled in, plus a blank sheet for you to try it yourself.
Google Sheets
Google Sheets supports AVERAGEIFS with the same syntax. One Sheets-only tip: you can wrap the result in ROUND(…, 2) to keep money averages tidy, since Sheets shows more decimals by default.
Example prices and figures are illustrative, not market data.