Syntax, a live playground, a measured conditional-average gallery, the #DIV/0! trap, and a sourced error library: the multi-condition AVERAGEIFS function.
Averages the cells in average_range that meet every criteria pair. Available since Excel 2007.
AVERAGEIFS averages cells that meet every condition. It is exactly SUMIFS divided by COUNTIFS — and this page's engine evaluates that decomposition live (AVERAGEIFS itself is not in HyperFormula 3.5). Every row was measured live. Click any row to load it.
| Expression | Result | Why |
|---|---|---|
| =SUMIFS(C2:C8,B2:B8,"East")/COUNTIFS(B2:B8,"East") | 225 | Average East sales |
| =SUMIFS(C2:C8,B2:B8,"West")/COUNTIFS(B2:B8,"West") | 200 | Average West sales |
| =SUMIFS(C2:C8,A2:A8,"Alice")/COUNTIFS(A2:A8,"Alice") | 116.66666667 | Average Alice sales |
| =SUMIFS(C2:C8,B2:B8,"East",C2:C8,">150")/COUNTIFS(B2:B8,"East",C2:C8,">150") | 325 | East AND sales>150 |
| =SUMIFS(C2:C8,B2:B8,"East") | 900 | The numerator: East total |
| =COUNTIFS(B2:B8,"East") | 4 | The denominator: East count |
| =IFERROR(SUMIFS(C2:C8,B2:B8,"Nope")/COUNTIFS(B2:B8,"Nope"),"no match") | no match | Guarded empty criteria |
| =SUMIFS(C2:C8,B2:B8,"<>East")/COUNTIFS(B2:B8,"<>East") | 200 | Not-East average |
| Expression | Result | Why |
|---|---|---|
| =AVERAGEIFS(C2:C8,B2:B8,"East") | 225 | average_range first, then criteria pairs |
| =AVERAGEIFS(C2:C8,B2:B8,"East",C2:C8,">150") | 325 | East AND sales>150 |
| =AVERAGEIFS(C2:C8,B2:B8,"<>East") | 200 | not-equal criteria |
| =AVERAGEIFS(C2:C8,A2:A8,"Alice",B2:B8,"East") | 125 | Alice AND East (100,150) |
Edit the table or type any formula — the HyperFormula engine evaluates the SUMIFS/COUNTIFS decomposition instantly. Load a preset to average a region, combine criteria, and guard an empty match.
8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.
AVERAGEIFS is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2007.
| Platform | Support | Notes | Gotcha |
|---|
AVERAGEIFS is one piece. The full course walks the toolkit end to end with live, measured examples.