The first thing most people do when they need an average of sales over $50,000 for the West region in Q2 is write {=AVERAGE(IF((B2:B100="West")*(C2:C100>50000),D2:D100))}. That’s a Ctrl+Shift+Enter array formula — fragile, slow, and impossible to audit. Worse: it breaks if someone presses Enter instead of Ctrl+Shift+Enter. You don’t need arrays. You need AVERAGEIFS.
Quick Answer
AVERAGEIFS calculates the arithmetic mean of cells that meet all specified criteria — and it’s non-array, fully dynamic, and reads left-to-right like plain English. Its syntax is AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). Unlike AVERAGEIF, it supports up to 127 conditions — and yes, it handles dates, wildcards, and even cell references as criteria.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| AVERAGEIFS (native) | 1. Select output cell 2. Type =AVERAGEIFS(D2:D12,B2:B12,"West",C2:C12,">50000")3. Press Enter |
Multi-criteria numeric averages (sales, scores, durations) | All criteria ranges must be same height/width as average_range |
| FILTER + AVERAGE (365/2021) | 1. Use =AVERAGE(FILTER(D2:D12,(B2:B12="West")*(C2:C12>50000)))2. Press Enter |
Dynamic arrays, complex logic (OR, nested conditions) | Not available in Excel 2019 or earlier |
| SUMIFS / COUNTIFS combo | 1. Calculate numerator: =SUMIFS(D2:D12,B2:B12,"West",C2:C12,">50000")2. Denominator: =COUNTIFS(B2:B12,"West",C2:C12,">50000")3. Divide them |
Learning how AVERAGEIFS works under the hood | More error-prone; #DIV/0! if no matches |
| PivotTable + Value Field Settings | 1. Insert PivotTable 2. Drag Region to Filters, Amount to Values 3. Right-click value → Summarize Values By → Average |
Exploratory analysis, ad-hoc slicing | Static unless refreshed; can’t reference in formulas |
Method 1 Deep Dive
Let’s walk through a realistic scenario. You manage regional sales data in Sheet1, rows 2–12:
| A | B | C | D |
|---|---|---|---|
| Name | Region | Sales Target ($) | Actual Sales ($) |
| Sarah Chen | West | 65000 | 72400 |
| Diego Morales | East | 42000 | 39800 |
| Priya Kapoor | West | 58000 | 61200 |
| Marcus Lee | North | 75000 | 68900 |
| Anya Petrova | West | 51000 | 54300 |
| Tariq Hassan | South | 48000 | 46100 |
| Lena Schmidt | West | 62000 | 66500 |
| James Wu | East | 55000 | 57200 |
| Fatima Ndiaye | West | 49000 | 42800 |
We want the average of Actual Sales (column D) where Region = "West" AND Sales Target > 50,000. The formula goes in cell F2:
=AVERAGEIFS(D2:D12,B2:B12,"West",C2:C12,">50000")
It returns $63,600. That’s the average of Sarah ($72,400), Priya ($61,200), Anya ($54,300), and Lena ($66,500). Fatima is excluded because her target is $49,000. The beauty? You can replace "West" with G1 and ">50000" with H1 — then change G1/H1 and the result updates instantly. No recalc needed.
Surprising tip: You can use wildcards. Try =AVERAGEIFS(D2:D12,A2:A12,"S*",C2:C12,">=60000") to average actual sales for all names starting with “S” whose target is ≥$60,000. It matches only Sarah Chen — and returns $72,400.
Method 2 Deep Dive
What if you need either West or North — but not both? AVERAGEIFS doesn’t support OR logic natively. That’s where FILTER shines — and here’s the elegant workaround:
In cell F5, try this (Excel 365 or 2021 only):
=AVERAGE(FILTER(D2:D12,(B2:B12="West")+(B2:B12="North")))
Note the + (not *). In Boolean math, + means OR, * means AND. This formula evaluates to TRUE for rows where Region is West OR North — then AVERAGE takes the mean of those Actual Sales values: $72,400 (Sarah), $61,200 (Priya), $66,500 (Lena), and $68,900 (Marcus) = $67,250.
Keyboard shortcut pro move: To quickly select the entire data range before writing your formula, click any cell inside it (say, C5), then press Ctrl+A twice — the first selects the current region, the second expands to the full used range. Much faster than dragging.
You can also embed date logic cleanly. If column E contains hire dates (e.g., 2022-04-12, 2023-09-05), this gets average sales for West reps hired after Jan 1, 2023:
=AVERAGEIFS(D2:D12,B2:B12,"West",E2:E12,">="&DATE(2023,1,1))
No quotes around the DATE function — because we’re concatenating a string condition. That’s a nuance most miss.
Cheat Sheet
| Task | Formula | Shortcut / Tip |
|---|---|---|
| Average West sales > $50k | =AVERAGEIFS(D2:D12,B2:B12,"West",C2:C12,">50000") |
Use Alt+M+U+M to open Formula Auditing → Evaluate Formula |
| Average for names starting with “S” | =AVERAGEIFS(D2:D12,A2:A12,"S*") |
Wildcards: * = any chars, ? = single char |
| Average West OR North | =AVERAGE(FILTER(D2:D12,(B2:B12="West")+(B2:B12="North"))) |
Requires Excel 365/2021; + = OR, * = AND |
| Average with cell-based criteria | =AVERAGEIFS(D2:D12,B2:B12,G1,C2:C12,">="&H1) |
Reference cells directly — no quotes around H1 if it holds a number |
| Exclude blanks in average_range | =AVERAGEIFS(D2:D12,D2:D12,"<>") |
"<>" means “not equal to empty” — catches blank cells & zeros |
