Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
AVERAGEIFS
AggregationAverage the values that match several conditions — the mean sibling of SUMIFS.
Difficulty
1What is it?
AVERAGEIFS returns the average of the cells in a range that meet one or more conditions. It completes the *IFS family alongside SUMIFS and COUNTIFS — use it for average deal size by stage, average days-to-pay by customer, or average ticket by month, straight off a flat table.
2What it looks like
AVERAGEIFS(average_range, criteria_range1, criteria1, …)- average_range
- The numbers to average (e.g. the Amount column).
- criteria_range1
- The column to test.
- criteria1
- The condition — text ("Closed"), a number, or an operator in quotes (">0").
3When you use it
- Average order value for one customer or channel.
- Average days-to-pay for invoices that are settled.
- Average margin within a range — combine a ">=" and a "<=" criterion.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Average the amounts for one account.
=AVERAGEIFS(B2:B5, A2:A5, "Revenue")$42,500| A | B | C | |
|---|---|---|---|
| 1 | Account | Amount | Result |
| 2 | Revenue | $80,000 | $42,500 |
| 3 | COGS | $32,000 | |
| 4 | Revenue | $5,000 | |
| 5 | Opex | $28,000 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#DIV/0!No rows match the criteria, so there's nothing to average.Fix: Wrap in IFERROR for a clean 0/blank, or loosen the criteria.
#VALUE!average_range and a criteria_range are different sizes.Fix: Make every range identical in height.
Skips blanksEmpty cells in average_range are ignored (not treated as 0).Fix: That's intended — fill blanks with 0 first if you want them counted.
6Better functions & alternatives
- AVERAGEIF — The single-condition version when you only test one column.
- SUMIFS ÷ COUNTIFS — Compute the average yourself when you also want the total and the count.
- PivotTable — Average across many cuts at once, interactively.
Want AVERAGEIFS already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →