On this page ⌄
- The AVERAGEIF syntax
- A basic AVERAGEIF example
- Writing conditions: text, numbers and comparisons
- Use a cell as the condition
- Partial matches with wildcards
- AVERAGEIFS: average with multiple criteria
- Average between two dates
- Average with OR conditions
- How zeros, blank cells and text are averaged
- TRUE and FALSE values
- When nothing matches: fix #DIV/0!
- Ranges of different sizes
- Common mistakes
- Frequently asked questions about AVERAGEIF and AVERAGEIFS
- Which versions of Excel have AVERAGEIF and AVERAGEIFS?
- Key takeaways
To average only the rows that meet a condition, use AVERAGEIF. Give it the cells to check, the condition and the cells to average. For more than one condition, use AVERAGEIFS, which takes the cells to average first:
=AVERAGEIF(D2:D11, "Monitor", F2:F11)→ $1,375=AVERAGEIFS(F2:F11, D2:D11, "Monitor", E2:E11, ">=5")→ $1,500The first formula is the average revenue of a monitor order. The second is the same average, but only for monitor orders of 5 units or more. This guide covers both functions and every kind of condition: text, numbers, cells, wildcards and dates. It also covers the parts that trip people up: either-or conditions, zeros that drag an average down, blank cells that are quietly skipped and the #DIV/0! error you get when nothing matches.
The examples use this sales log in A1:F11, the same one as the SUMIF and SUMIFS guide and the COUNTIF guide:
| A: Date | B: Region | C: Rep | D: Product | E: Units | F: Revenue | |
|---|---|---|---|---|---|---|
| 2 | Jan 4, 2026 | North | Ava | Laptop | 3 | $3,600 |
| 3 | Jan 9, 2026 | South | Leo | Monitor | 5 | $1,250 |
| 4 | Jan 15, 2026 | North | Maya | Laptop | 2 | $2,400 |
| 5 | Jan 22, 2026 | East | Sam | Keyboard | 12 | $720 |
| 6 | Feb 3, 2026 | West | Zoe | Monitor | 4 | $1,000 |
| 7 | Feb 11, 2026 | North | Ava | Keyboard | 8 | $480 |
| 8 | Feb 19, 2026 | South | Ivy | Laptop | 4 | $4,800 |
| 9 | Feb 27, 2026 | East | Sam | Monitor | 6 | $1,500 |
| 10 | Mar 6, 2026 | West | Noah | Laptop | 1 | $1,200 |
| 11 | Mar 14, 2026 | North | Maya | Monitor | 7 | $1,750 |
The AVERAGEIF syntax
=AVERAGEIF(range, criteria, [average_range])| Argument | What it means | Example |
|---|---|---|
| range | The cells to check against the condition | D2:D11 |
| criteria | The condition: text, a number, a comparison or a cell | "Monitor" |
| average_range | Optional. The cells to average for the rows that match. Leave it out to average range itself | F2:F11 |
AVERAGEIF works like SUMIF, with the same three arguments in the same order. The difference is that it divides the total by the number of cells it added up.
A basic AVERAGEIF example
What's the average revenue of a monitor order?
=AVERAGEIF(D2:D11, "Monitor", F2:F11)→ $1,375AVERAGEIF walks down D2:D11, and every time it finds Monitor it takes the revenue from the same row: 1,250, 1,000, 1,500 and 1,750. Then it divides their total, 5,500, by 4. You'd get the same answer by dividing a SUMIF by a COUNTIF:
=SUMIF(D2:D11, "Monitor", F2:F11) / COUNTIF(D2:D11, "Monitor")→ $1,375That longer version comes in handy for either-or conditions later on.
When the condition is about the numbers you're averaging, leave out average_range. What's the average size of an order over $2,000?
=AVERAGEIF(F2:F11, ">2000")→ $3,600Three orders are over $2,000: $3,600, $2,400 and $4,800. AVERAGEIF adds them up and divides by 3.
Your turn. The sheet below is live and uses the same sales log. This exercise asks for a total rather than an average, so it uses SUMIF, but the arguments are the ones you've just seen: the range to check, the condition, then the range to add up.
Try it yourself
A live spreadsheet. Type a formula and press Enter to check it.
What was the total revenue from the North region?
Write your formula in the green cell, I2.
Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.
Once you have the total, swap SUMIF for AVERAGEIF, keep the same three arguments, and you get the region's average order instead.
Writing conditions: text, numbers and comparisons
The criteria argument is where most of the work happens. These patterns cover nearly everything:
| You want | Write | Notes |
|---|---|---|
| Equal to some text | "Monitor" | Not case-sensitive |
| Equal to a number | 4 | No quotes needed |
| Greater than a number | ">2000" | The operator goes inside the quotes |
| At least a number | ">=5" | "<=" means at most |
| Not equal to | "<>South" | |
| Equal to a cell | H2 | No quotes around a cell reference |
| Compared with a cell | ">="&H3 | Join the operator to the cell with & |
| Contains some text | "*board*" | * means any characters |
| Not zero | "<>0" | Leaves zeros out of the average |
| Blank | "" | |
| Not blank | "<>" |
A number on its own needs no quotes. What's the average revenue of the orders with exactly 4 units?
=AVERAGEIF(E2:E11, 4, F2:F11)→ $2,900That's Zoe's $1,000 and Ivy's $4,800. With an operator, the whole condition goes in quotes. Here are the orders of 5 units or more, and every region except South:
=AVERAGEIF(E2:E11, ">=5", F2:F11)→ $1,140=AVERAGEIF(B2:B11, "<>South", F2:F11)→ $1,581.25The orders with the most units bring in less on average, because they're all monitors and keyboards.
Use a cell as the condition
Typing the condition into the formula works, but pointing at a cell is more useful: change the cell and the average updates. If H2 holds a rep's name, such as Ava:
=AVERAGEIF(C2:C11, H2, F2:F11)→ $2,040To combine an operator with a cell, join them with &. If H3 holds 1500, this averages the orders worth $1,500 or more:
=AVERAGEIF(F2:F11, ">="&H3)→ $2,810Five orders qualify, from $1,500 up to $4,800. Don't put the cell inside the quotes. ">=H3" compares the revenue with the text H3, matches nothing and returns #DIV/0!.
An empty condition cell is a trap too. Microsoft's documentation says AVERAGEIF treats an empty criteria cell as 0, and there's no rep called 0, so =AVERAGEIF(C2:C11, H2, F2:F11) returns #DIV/0! until you type a name in H2.
To build a summary table, put each value you want an average for in its own cell and copy one formula down. With East, North, South and West in L2:L5, type this in M2 and copy it to M5:
=AVERAGEIF($B$2:$B$11, L2, $F$2:$F$11)→ $1,110That's East's average order. The dollar signs keep both ranges fixed while L2 moves down to L3, L4 and L5. Without them, the ranges slide down a row with each copy and drop orders from the top. The absolute vs relative references guide shows exactly how that goes wrong.
Partial matches with wildcards
In a text condition, an asterisk * stands for any number of characters. What's the average revenue of the products that end in board?
=AVERAGEIF(D2:D11, "*board", F2:F11)→ $600Keyboard is the only one, so that's the average of $720 and $480. A question mark ? stands for exactly one character. This averages the reps whose second letter is o, Zoe and Noah:
=AVERAGEIF(C2:C11, "?o*", F2:F11)→ $1,100Wildcards only work on text, so "1*" won't find the revenue figures that start with 1. To match a real asterisk or question mark, put a tilde in front of it: "*~?" matches text that ends in a question mark.
AVERAGEIFS: average with multiple criteria
AVERAGEIFS averages a row only when every condition is true. Write the range to average first, followed by pairs of range and condition:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | What it means | Example |
|---|---|---|
| average_range | The cells to average | F2:F11 |
| criteria_range1 | The cells to check against the first condition | D2:D11 |
| criteria1 | The first condition | "Monitor" |
| criteria_range2, criteria2, ... | Optional. More pairs of range and condition, up to 127 pairs in all | E2:E11, ">=5" |
What's the average revenue of the monitor orders with at least 5 units?
=AVERAGEIFS(F2:F11, D2:D11, "Monitor", E2:E11, ">=5")→ $1,500There are four monitor orders, but Zoe's had only 4 units, so three are left: $1,250, $1,500 and $1,750. The conditions can use every pattern from the table above, and two conditions can test the same column. To average the orders with 3 to 6 units, test the units column twice:
=AVERAGEIFS(F2:F11, E2:E11, ">=3", E2:E11, "<=6")→ $2,430Notice the order. AVERAGEIF puts the range to average last, but AVERAGEIFS puts it first:
| AVERAGEIF | AVERAGEIFS | |
|---|---|---|
| Conditions | One | 1 to 127 |
| Where the average range goes | Last | First |
| Can you skip the average range? | Yes, then it averages range | No |
| Ranges of different sizes | Quietly resized, see below | #VALUE! |
Average between two dates
To average a date range, use two conditions on the same date column, one for the start and one for the end. What was the average order in February 2026?
=AVERAGEIFS(F2:F11, A2:A11, ">="&DATE(2026,2,1), A2:A11, "<"&DATE(2026,3,1))→ $1,945Four orders fall in February: $1,000, $480, $4,800 and $1,500. The SUMIF guide totals the same month at $7,780, and 7,780 divided by 4 is 1,945. DATE builds a real date, and & joins it to the operator. "Before March 1" works for any month, without having to know how many days it has.
For only one end, AVERAGEIF is enough. Here's the average order placed before February 1, which means January:
=AVERAGEIF(A2:A11, "<"&DATE(2026,2,1), F2:F11)→ $1,992.50With the start date in H4 and the end date in H5, the conditions point at the cells:
=AVERAGEIFS(F2:F11, A2:A11, ">="&H4, A2:A11, "<="&H5)"<=" includes the end date as long as the dates have no times. If they do, use "<" and the day after the end, as in the February example.
You can't wrap the date column in a function inside AVERAGEIFS, as in MONTH(A2:A11). Its ranges must be real cell ranges, and Excel won't accept the formula. To average by month without the two dates, use AVERAGE with FILTER, covered in the next section: =AVERAGE(FILTER(F2:F11, MONTH(A2:A11)=2)) also returns $1,945.
Average with OR conditions
AVERAGEIFS only does AND logic: every condition must be true. For OR, such as monitors or keyboards, add up the totals and divide by the counts:
=(SUMIF(D2:D11, "Monitor", F2:F11) + SUMIF(D2:D11, "Keyboard", F2:F11)) / (COUNTIF(D2:D11, "Monitor") + COUNTIF(D2:D11, "Keyboard"))→ $1,116.67That's $6,700 of revenue from six orders. A shorter way to write it puts both products in curly brackets, an array constant. SUMIF and COUNTIF then return one result for each product, and SUM adds them up:
=SUM(SUMIF(D2:D11, {"Monitor","Keyboard"}, F2:F11)) / SUM(COUNTIF(D2:D11, {"Monitor","Keyboard"}))→ $1,116.67Monitors and keyboards are everything except laptops, so here a single "not equal to" condition does the same job: =AVERAGEIF(D2:D11, "<>Laptop", F2:F11) also returns $1,116.67.
In Microsoft 365 and Excel 2021 or later, AVERAGE with FILTER is the most flexible option. FILTER keeps the rows that pass either test, and AVERAGE averages them:
=AVERAGE(FILTER(F2:F11, (D2:D11="Monitor")+(D2:D11="Keyboard")))→ $1,116.67The + between the tests means OR, as the FILTER guide explains. FILTER earns its place when the OR spans two columns. Here are the West orders, plus any order with more than 6 units:
=AVERAGE(FILTER(F2:F11, (B2:B11="West")+(E2:E11>6)))→ $1,030Adding a SUMIF for each condition would count an order twice if it matched both. FILTER keeps each row once. If no row matches, FILTER returns #CALC!, so add IFERROR when that can happen.
How zeros, blank cells and text are averaged
AVERAGEIF and AVERAGEIFS only average numbers, and a zero is a number. South has two orders, Leo's $1,250 and Ivy's $4,800:
=AVERAGEIF(B2:B11, "South", F2:F11)→ $3,025Now say Leo's order was cancelled. Here's what the same formula returns, depending on what you put in F3:
| F3 holds | Result | Why |
|---|---|---|
| 1250 | $3,025 | Both orders are averaged |
| 0 | $2,400 | A zero counts: (0 + 4,800) ÷ 2 |
| Nothing | $4,800 | Empty cells in the average range are skipped |
| Cancelled | $4,800 | Text is skipped too |
The empty cell and the text drop out of the average completely, as if Leo's order wasn't there. Numbers stored as text, which often arrive with data from other systems, are skipped the same way, so an average can be off without any error. If a blank should count as 0, type 0 in it. Or divide a SUMIF by a COUNTIF, which counts the row whatever its revenue cell holds. With F3 empty, =SUMIF(B2:B11, "South", F2:F11) / COUNTIF(B2:B11, "South") returns $2,400.
Zeros are the opposite problem. When a 0 means "no data" rather than a real result, leave zeros out with the condition "<>0" on the average range itself. With 0 in F3:
=AVERAGEIFS(F2:F11, B2:B11, "South", F2:F11, "<>0")→ $4,800The same range can be both the range to average and a criteria range. Without other conditions, =AVERAGEIF(F2:F11, "<>0") averages a whole column without its zeros. Use ">0" instead to leave out negative numbers, such as refunds, as well.
TRUE and FALSE values
Microsoft's two help pages describe TRUE and FALSE differently. The AVERAGEIF page says cells in range that contain TRUE or FALSE are ignored. The AVERAGEIFS page says cells that contain TRUE evaluate as 1 and cells that contain FALSE as 0. Rather than rely on either rule, don't average a column of TRUE and FALSE values. Use it as a condition instead.
Say column G held TRUE for orders from repeat customers and FALSE for new ones. This averages the revenue of the repeat orders:
=AVERAGEIFS(F2:F11, G2:G11, TRUE)For the share of orders that came from repeat customers, count the TRUE values and divide by the number of orders: =COUNTIF(G2:G11, TRUE)/COUNTA(G2:G11).
When nothing matches: fix #DIV/0!
An average divides by the number of matching cells. When no row matches, that number is 0, and you get #DIV/0!. There's no Central region:
=AVERAGEIF(B2:B11, "Central", F2:F11)→ #DIV/0!The same happens when rows match but none of their cells in the average range holds a number. To show something friendlier, wrap the formula in IFERROR:
=IFERROR(AVERAGEIF(B2:B11, "Central", F2:F11), "No orders")→ No ordersUse "" instead of a message to leave the cell looking blank. A 0 is usually a poor choice here, because an average of $0 looks like a real result.
IFERROR hides every error, though, including mistakes you'd want to see, such as the #VALUE! AVERAGEIFS returns when its ranges are different sizes. To catch only the empty case, count the matches first and test the count with IF:
=IF(COUNTIF(B2:B11, "Central")=0, "No orders", AVERAGEIF(B2:B11, "Central", F2:F11))→ No ordersFor AVERAGEIFS, count with COUNTIFS and the same conditions.
Ranges of different sizes
In AVERAGEIFS, the average range and every criteria range must be the same size, or you get #VALUE!. AVERAGEIF doesn't check. It takes the first cell of average_range and stretches it to the size of range, so =AVERAGEIF(D2:D11, "Monitor", F2) still returns $1,375. That sounds handy, but it hides mistakes. Here the average range starts one row too low:
=AVERAGEIF(D2:D11, "Monitor", F3:F11)→ $1,360=AVERAGEIFS(F3:F11, D2:D11, "Monitor")→ #VALUE!AVERAGEIF reads F3:F11 as F3:F12, so each monitor row picks up the revenue from the row below it: $2,400, $480, $1,200 and the empty F12, which is skipped. The result is close enough to the real $1,375 to look right. AVERAGEIFS stops at the mismatched ranges and tells you something is wrong. Whichever you use, start every range on the same row and end it on the same row.
Common mistakes
| Problem | What's going on | Fix |
|---|---|---|
#DIV/0! | No row matched, or the matching rows have no numbers to average | Check the condition for typos and extra spaces, then add IFERROR or a COUNTIF test |
#DIV/0! with only two arguments | range holds text, so there's nothing to average | Add average_range as the third argument |
#VALUE! in AVERAGEIFS | The ranges are different sizes | Make every range cover the same rows |
| A believable but wrong AVERAGEIF result | average_range starts on a different row from range | Start and end every range on the same rows |
| Wrong argument order | AVERAGEIF puts the average range last, AVERAGEIFS puts it first | Use AVERAGEIFS everywhere |
">="H3 doesn't work | The operator and cell weren't joined | Write ">="&H3 |
| The average is too low | Zeros are averaged | Add the condition "<>0" on the average range |
| Some rows are left out of the average | Empty cells, text and numbers stored as text are skipped | Type 0 where a blank should count, and convert text to numbers |
| The wrong answer for an OR | Two averages were averaged | Divide the total of the SUMIFs by the total of the COUNTIFs |
| Copied formulas give odd results | The ranges moved as the formula was copied | Lock them: $B$2:$B$11 |
Frequently asked questions about AVERAGEIF and AVERAGEIFS
Is AVERAGEIF case-sensitive?
No. "monitor", "Monitor" and "MONITOR" all match the same rows. For a case-sensitive average in Microsoft 365 or Excel 2021 or later, put EXACT inside FILTER:
=AVERAGE(FILTER(F2:F11, EXACT(D2:D11, "Monitor")))→ $1,375EXACT only matches Monitor with a capital M, so with "monitor" in lower case, FILTER finds nothing and the formula returns #CALC!.
How do I calculate a weighted average with a condition?
AVERAGEIF gives every matching row the same weight. To weight by another column, divide one SUMIFS by another. The average revenue per monitor sold, weighted by units, is the total monitor revenue divided by the total monitor units:
=SUMIFS(F2:F11, D2:D11, "Monitor") / SUMIFS(E2:E11, D2:D11, "Monitor")→ $250That's $5,500 from 22 units.
Is there a MEDIANIF or MAXIF function?
There's no MEDIANIF. In Microsoft 365 and Excel 2021 or later, put FILTER inside MEDIAN. February's median order is much lower than its $1,945 average, because Ivy's $4,800 laptop pulls the average up:
=MEDIAN(FILTER(F2:F11, MONTH(A2:A11)=2))→ $1,250For the largest and smallest values, use MAXIFS and MINIFS. They work like AVERAGEIFS, with the range first: =MAXIFS(F2:F11, D2:D11, "Monitor") returns $1,750, the biggest monitor order. They need Excel 2019 or later, or Microsoft 365.
Does AVERAGEIF ignore hidden or filtered rows?
No. AVERAGEIF and AVERAGEIFS include every row in their ranges, hidden or not. To average only the rows you can see, filter the table and use =SUBTOTAL(101, F2:F11). SUBTOTAL doesn't take a condition of its own, so the filter does that job.
Which versions of Excel have AVERAGEIF and AVERAGEIFS?
AVERAGEIF and AVERAGEIFS work in Excel 2007 and every version since, including Microsoft 365, on Windows and Mac, and Excel for the web. So do the SUMIF and COUNTIF versions of the OR formulas, array constants included. AVERAGE with FILTER needs Microsoft 365 or Excel 2021 or later, and MAXIFS and MINIFS need Excel 2019 or later.
In Google Sheets, AVERAGEIF and AVERAGEIFS have the same arguments in the same order, and the conditions, wildcards and IFERROR fix work the same way. For Microsoft's own examples, see the AVERAGEIF function and AVERAGEIFS function pages.
For more practice, the conditional math topic has the free sumif-region and countif-product exercises on the same sales log, plus Premium exercises on one rep's average with AVERAGEIF and two conditions with AVERAGEIFS.
Key takeaways
=AVERAGEIF(range, criteria, [average_range])averages the rows that match one condition.=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)handles one or more, with the range to average first.- Put operators inside quotes and join them to cells or dates with
&, as in">="&H3. For a date range, use two conditions on the same column. - Zeros are averaged, but empty cells and text in the average range are skipped. Add
"<>0"to leave zeros out. - When nothing matches, you get #DIV/0!. Wrap the formula in IFERROR, or test with COUNTIF first. For OR conditions, divide the SUMIFs by the COUNTIFs, or use AVERAGE with FILTER.
Practice
Keep going with AVERAGEIF and AVERAGEIFS exercises
Reading gets you started. Solving a few of these is what makes it stick.
- 1Average for one rep PremiumOn average, how many units did Sam sell per order?→
- 2AVERAGEIFS PremiumWhat was the average revenue of North orders with more than 2 units?→
- 3Revenue for one regionFreeWhat was the total revenue from the North region?→
- 4Count one productFreeHow many orders were for Laptops?→

About the author
Brian Millot
Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.