∑ifConditional math15 min read ·

How to Use AVERAGEIF and AVERAGEIFS in Excel

Learn AVERAGEIF and AVERAGEIFS with real examples. Text, numbers, dates, wildcards, multiple criteria, OR logic, leaving out zeros and fixing #DIV/0!.

Quick answer

=AVERAGEIF(range, criteria, [average_range])

AVERAGEIF averages the cells that meet one condition, like =AVERAGEIF(D2:D11,"Monitor",F2:F11). AVERAGEIFS averages the cells that meet several conditions at once, and its range to average comes first. When nothing matches, both return #DIV/0!.

Arguments in [brackets] are optional.

On this page ⌄
  1. The AVERAGEIF syntax
  2. A basic AVERAGEIF example
  3. Writing conditions: text, numbers and comparisons
  4. Use a cell as the condition
  5. Partial matches with wildcards
  6. AVERAGEIFS: average with multiple criteria
  7. Average between two dates
  8. Average with OR conditions
  9. How zeros, blank cells and text are averaged
  10. TRUE and FALSE values
  11. When nothing matches: fix #DIV/0!
  12. Ranges of different sizes
  13. Common mistakes
  14. Frequently asked questions about AVERAGEIF and AVERAGEIFS
  15. Which versions of Excel have AVERAGEIF and AVERAGEIFS?
  16. 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,500

The 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: DateB: RegionC: RepD: ProductE: UnitsF: Revenue
2Jan 4, 2026NorthAvaLaptop3$3,600
3Jan 9, 2026SouthLeoMonitor5$1,250
4Jan 15, 2026NorthMayaLaptop2$2,400
5Jan 22, 2026EastSamKeyboard12$720
6Feb 3, 2026WestZoeMonitor4$1,000
7Feb 11, 2026NorthAvaKeyboard8$480
8Feb 19, 2026SouthIvyLaptop4$4,800
9Feb 27, 2026EastSamMonitor6$1,500
10Mar 6, 2026WestNoahLaptop1$1,200
11Mar 14, 2026NorthMayaMonitor7$1,750

The AVERAGEIF syntax

=AVERAGEIF(range, criteria, [average_range])
ArgumentWhat it meansExample
rangeThe cells to check against the conditionD2:D11
criteriaThe condition: text, a number, a comparison or a cell"Monitor"
average_rangeOptional. The cells to average for the rows that match. Leave it out to average range itselfF2: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,375

AVERAGEIF 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,375

That 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,600

Three 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.

Exercise 1 of 11SUMIF

What was the total revenue from the North region?

Write your formula in the green cell, I2.

q1-orders.xlsx
fx
1DateRegionRepProductUnitsRevenue
2Jan 4, 2026NorthAvaLaptop3$3,600.00North revenue
3Jan 9, 2026SouthLeoMonitor5$1,250.00
4Jan 15, 2026NorthMayaLaptop2$2,400.00
5Jan 22, 2026EastSamKeyboard12$720.00
6Feb 3, 2026WestZoeMonitor4$1,000.00
7Feb 11, 2026NorthAvaKeyboard8$480.00
8Feb 19, 2026SouthIvyLaptop4$4,800.00
9Feb 27, 2026EastSamMonitor6$1,500.00
10Mar 6, 2026WestNoahLaptop1$1,200.00
11Mar 14, 2026NorthMayaMonitor7$1,750.00
12
13

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 wantWriteNotes
Equal to some text"Monitor"Not case-sensitive
Equal to a number4No 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 cellH2No quotes around a cell reference
Compared with a cell">="&H3Join 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,900

That'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.25

The 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,040

To 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,810

Five 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,110

That'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)→ $600

Keyboard 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,100

Wildcards 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], ...)
ArgumentWhat it meansExample
average_rangeThe cells to averageF2:F11
criteria_range1The cells to check against the first conditionD2:D11
criteria1The first condition"Monitor"
criteria_range2, criteria2, ...Optional. More pairs of range and condition, up to 127 pairs in allE2: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,500

There 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,430

Notice the order. AVERAGEIF puts the range to average last, but AVERAGEIFS puts it first:

AVERAGEIFAVERAGEIFS
ConditionsOne1 to 127
Where the average range goesLastFirst
Can you skip the average range?Yes, then it averages rangeNo
Ranges of different sizesQuietly 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,945

Four 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.50

With 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.67

That'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.67

Monitors 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.67

The + 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,030

Adding 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,025

Now say Leo's order was cancelled. Here's what the same formula returns, depending on what you put in F3:

F3 holdsResultWhy
1250$3,025Both orders are averaged
0$2,400A zero counts: (0 + 4,800) ÷ 2
Nothing$4,800Empty cells in the average range are skipped
Cancelled$4,800Text 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,800

The 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 orders

Use "" 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 orders

For 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

ProblemWhat's going onFix
#DIV/0!No row matched, or the matching rows have no numbers to averageCheck the condition for typos and extra spaces, then add IFERROR or a COUNTIF test
#DIV/0! with only two argumentsrange holds text, so there's nothing to averageAdd average_range as the third argument
#VALUE! in AVERAGEIFSThe ranges are different sizesMake every range cover the same rows
A believable but wrong AVERAGEIF resultaverage_range starts on a different row from rangeStart and end every range on the same rows
Wrong argument orderAVERAGEIF puts the average range last, AVERAGEIFS puts it firstUse AVERAGEIFS everywhere
">="H3 doesn't workThe operator and cell weren't joinedWrite ">="&H3
The average is too lowZeros are averagedAdd the condition "<>0" on the average range
Some rows are left out of the averageEmpty cells, text and numbers stored as text are skippedType 0 where a blank should count, and convert text to numbers
The wrong answer for an ORTwo averages were averagedDivide the total of the SUMIFs by the total of the COUNTIFs
Copied formulas give odd resultsThe ranges moved as the formula was copiedLock 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,375

EXACT 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")→ $250

That'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,250

For 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.

  1. 1Average for one rep PremiumOn average, how many units did Sam sell per order?→
  2. 2AVERAGEIFS PremiumWhat was the average revenue of North orders with more than 2 units?→
  3. 3Revenue for one regionFreeWhat was the total revenue from the North region?→
  4. 4Count one productFreeHow many orders were for Laptops?→
All conditional math exercises →
Brian Millot

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.

Related guides

Practice makes formulas

Stop reading. Start typing formulas.

Every exercise is a real spreadsheet with instant feedback. The first two in every topic are free, no sign-up needed.