AVERAGEIF and AVERAGEIFS, explained with practice
AVERAGEIF returns the average of only those rows that meet a condition. Think average travel costs in the Sales team, or the typical online order value in one region.
In short: =AVERAGEIF(range,criteria,average_range) for one condition and =AVERAGEIFS(average_range,criteria_range1,criteria1,…) for several. Watch the order: AVERAGEIFS puts the numbers first, AVERAGEIF puts them last.
Try it now
The sheet lists travel costs for staff in three departments. Work out the average for Sales only. Click the yellow cell and type the formula.
Task In E2, calculate the average travel costs of the Sales department.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Department | Travel costs $ | Average, Sales | |
| 2 | Jana Weber | Sales | 640 | ||
| 3 | Murat Kaya | Purchasing | 310 | ||
| 4 | Lena Hoffmann | Sales | 820 | ||
| 5 | Tom Schulz | IT | 450 | ||
| 6 | Sara Klein | Sales | 510 | ||
| 7 | Ali Demir | Purchasing | 280 | ||
| 8 | Paul Becker | Sales | 730 |
The AVERAGEIF syntax
| Argument | Meaning | In the example |
|---|---|---|
| range | The cells that are tested | B2:B8 (the departments) |
| criteria | The condition: text, a number, a comparison or a cell reference | "Sales" |
| average_range | Optional. The numbers that get averaged | C2:C8 (the costs) |
=AVERAGEIF(B2:B8,"Sales",C2:C8) walks down the rows. Where column B says Sales, the value in column C is included. Excel then divides the total of those values by how many there are, which is SUMIF divided by COUNTIF in a single step.
Blank cells in the average range are skipped, zeros are not. It is the same rule as in AVERAGE: a 0 pulls the result down, an empty cell has no effect.
Average with multiple criteria: AVERAGEIFS
When two things must be true at once, for example a region and a sales channel, switch to AVERAGEIFS. Like SUMIFS, it takes the average range first and then pairs of range and criteria. Every pair has to match for a row to count, so the conditions work as AND.
Task In E2, calculate the average order value of online orders in the South region.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Channel | Order value $ | South, Online | |
| 2 | South | Online | 84 | ||
| 3 | North | Store | 120 | ||
| 4 | South | Store | 95 | ||
| 5 | South | Online | 132 | ||
| 6 | West | Online | 70 | ||
| 7 | North | Online | 101 | ||
| 8 | South | Online | 66 | ||
| 9 | South | Store | 150 |
The answer is =AVERAGEIFS(C2:C9,A2:A9,"South",B2:B9,"Online"). You can add up to 127 range and criteria pairs.
Mind the argument order
The two functions look almost identical, but the numbers sit at opposite ends:
| Function | Formula |
|---|---|
| AVERAGEIF | =AVERAGEIF(A2:A9,"South",C2:C9) |
| AVERAGEIFS | =AVERAGEIFS(C2:C9,A2:A9,"South") |
Both return the same value, so AVERAGEIFS works fine with a single condition too. If you stick to AVERAGEIFS you only need one pattern, and it matches SUMIFS, COUNTIFS and MAXIFS.
One more: averaging values above a threshold
The condition does not need its own column. You can test the numbers themselves, for instance to average only the large orders. Leave out the average range and Excel averages the cells it tested.
Task In E2, calculate the average amount of all orders of 1,000 or more. An order of exactly 1,000 counts.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Sales rep | Amount $ | Avg, 1,000 or more | |
| 2 | A-2041 | Eva Richter | 450 | ||
| 3 | A-2017 | Noah Wolf | 1200 | ||
| 4 | A-2063 | Mia Wagner | 980 | ||
| 5 | A-2009 | Eva Richter | 2300 | ||
| 6 | A-2052 | Paul Becker | 1000 | ||
| 7 | A-2034 | Noah Wolf | 760 | ||
| 8 | A-2028 | Mia Wagner | 1550 |
Comparisons always go in quotes. ">=1000" means 1,000 or more, ">1000" means more than 1,000. To keep the threshold in a cell such as F1, join it with an ampersand: =AVERAGEIF(C2:C8,">="&F1).
Common AVERAGEIF problems
#DIV/0!
No row met the condition, so there is nothing to average. The usual cause is a typo in the criteria or a trailing space in the data. If an empty result is expected, wrap the formula in IFERROR: =IFERROR(AVERAGEIF(B2:B8,"Sales",C2:C8),"").
The AVERAGEIF order inside AVERAGEIFS
=AVERAGEIFS(A2:A9,"South",C2:C9) fails because AVERAGEIFS expects a range in second place. The numbers belong at the front.
Ranges of different sizes
AVERAGEIFS needs every range to be the same size and returns #VALUE! otherwise. AVERAGEIF is more forgiving: it quietly resizes a shorter average range to match the tested range. That can give a wrong answer with no warning, so keep your ranges the same length.
Numbers stored as text
Values stored as text are not averaged. If the result looks off, COUNT tells you how many real numbers the range holds.
AVERAGEIF in German Excel
A German Excel calls the functions MITTELWERTWENN and MITTELWERTWENNS, with semicolons between arguments:
| English | German |
|---|---|
=AVERAGEIF(B2:B8,"Sales",C2:C8) | =MITTELWERTWENN(B2:B8;"Vertrieb";C2:C8) |
=AVERAGEIFS(C2:C9,A2:A9,"South",B2:B9,"Online") | =MITTELWERTWENNS(C2:C9;A2:A9;"Süd";B2:B9;"Online") |
Our exercises accept both spellings. Pick your Excel language below each exercise.
Which Excel versions have AVERAGEIF?
AVERAGEIF and AVERAGEIFS are in every current version: Microsoft 365, Excel 2024, 2021, 2019 and 2016, Excel for Mac and Excel for the web.
FAQ
How do I average a range but ignore zeros?
Use "not equal to 0" as the criteria: =AVERAGEIF(C2:C20,"<>0"). Blank cells are skipped anyway, so only real values remain.
Can AVERAGEIFS use OR logic?
No, every condition is joined with AND. For North or South, divide the combined total by the combined count: =(SUMIF(A2:A9,"North",C2:C9)+SUMIF(A2:A9,"South",C2:C9))/(COUNTIF(A2:A9,"North")+COUNTIF(A2:A9,"South")).
How do I average between two dates?
Test the date column twice, once per limit: =AVERAGEIFS(C2:C50,A2:A50,">="&DATE(2026,1,1),A2:A50,"<"&DATE(2026,4,1)) averages the first quarter of 2026.
Do wildcards work?
Yes. "Ber*" matches anything starting with Ber and "?" stands for exactly one character. Wildcards only apply to text criteria.