AVERAGEIF

How AVERAGEIF works

AVERAGEIF walks through the criteria range, checks each cell against your condition, and averages the matching values from the average_range. If you skip the third argument, it averages the criteria range itself (single-column shortcut for numeric thresholds).

Criteria can be a literal value ("North"), a comparison string (">1000", "<>0"), a cell reference (E1), or a wildcard pattern ("PO-*"). The same flexibility you have in SUMIF and COUNTIF, just averaged instead of summed or counted.

Empty cells in the average_range are skipped (they don't drag the average toward zero). Cells containing text or errors are also skipped. Zero values ARE included, so a row with $0 in revenue pulls the average down, which is usually what you want for a true mean.

= AVERAGEIF(range, criteria, [average_range])

Arguments

AVERAGEIF basic: average sales of North reps

Sales reps in column A with their region in B and Q4 sales in C. Question for the VP: what was the average Q4 sales per North rep? AVERAGEIF reads the region column, picks the rows where region equals North, and averages the matching Q4 numbers.

=AVERAGEIF(B2:B11, "North", C2:C11)

• •B2:B11 -> the region column (criteria range)
• •"North" -> the literal text to match, in quotes
• •C2:C11 -> the column to actually average (Q4 sales)
• •Three Norths -> rows 2, 5, and 8 match. AVERAGEIF averages just those three numbers.
• •Result -> $4,800 - the mean of $4,200, $5,800, and $4,400

AVERAGEIF with comparison: average orders above $1,000

Two-argument AVERAGEIF: pass only the range and a comparison string, and Excel averages the cells in that range that match. Useful when you want the mean of values above or below a threshold without a separate criteria column.

=AVERAGEIF(B2:B11, ">1000")

• •B2:B11 -> the order-amount column (acts as both range and average_range)
• •">1000" -> comparison criterion - strictly greater than 1000
• •No third arg -> Excel uses the range itself as the average_range
• •Five rows above $1,000 -> PO-1002, PO-1004, PO-1005, PO-1007, PO-1009
• •Result -> $2,764 - the mean of $2,400 + $3,150 + $1,250 + $5,020 + $2,000 = $13,820 / 5

AVERAGEIF with wildcards: average POs by prefix

Wildcards work the same way as in SUMIF and COUNTIF. Use `*` to match any string of characters and `?` to match a single character. Useful when product codes have a category prefix and you want the average for one category.

=AVERAGEIF(A2:A11, "PO-*", B2:B11)

• •A2:A11 -> the order-code column
• •"PO-*" -> matches any code starting with PO-
• •B2:B11 -> the amount column to average
• •Six PO- codes -> the rest are ON- (online) or SR- (subscription)
• •Result -> $2,115 - sum of $450 + $2,400 + $3,150 + $1,250 + $5,020 + $420 = $12,690 divided by 6

AVERAGEIFS: multiple criteria at once

When one criterion is not enough, AVERAGEIFS lets you stack them. The argument order is different from AVERAGEIF: average_range comes FIRST, then the criteria pairs.

Below: average salary of employees who are both in Engineering AND have status Active. A row has to clear BOTH filters to be included.

=AVERAGEIFS(C2:C11, A2:A11, "Engineering", B2:B11, "Active")

• •C2:C11 -> the column to average (Salary) - comes FIRST in AVERAGEIFS
• •A2:A11, "Engineering" -> first criteria pair: department equals Engineering
• •B2:B11, "Active" -> second criteria pair: status equals Active
• •Three matches -> Sarah, James, and Alex are all Engineering + Active
• •Result -> $91,333 - the mean of $95,000, $88,000, $91,000

AVERAGEIFS by date range: monthly KPI snapshot

Stack two date criteria to constrain a window. Use >= for the start date and <= for the end date. Same shape as before, just both criteria range to the same column.

=AVERAGEIFS(B2:B11, A2:A11, ">=2026-03-01", A2:A11, "<=2026-03-31")

• •B2:B11 -> the order-amount column (average_range)
• •A2:A11, ">=2026-03-01" -> first criteria: dates on or after March 1
• •A2:A11, "<=2026-03-31" -> second criteria: dates on or before March 31
• •Three March rows -> the 8th, 14th, and 27th
• •Result -> $1,217 - mean of $980, $1,420, $1,250

Where people go wrong

1. 01
   Argument order flip between AVERAGEIF and AVERAGEIFS

   AVERAGEIF takes range, criteria, [avg_range]. AVERAGEIFS takes avg_range FIRST, then pairs of range/criteria. Copying an AVERAGEIF and adding an extra pair gives a #VALUE! error because the first range is in the wrong slot.

   Fix: Rewrite from scratch when you switch to AVERAGEIFS. Put the column you want to average first, then add criteria pairs. Same rule applies to SUMIFS, MAXIFS, MINIFS.
2. 02
   Comparison operator outside the quotes

   Writing AVERAGEIF(B2:B11, > 1000) returns #NAME? because Excel can't parse the comparison without the operator and the value being a single string.

   Fix: Always wrap the comparison: ">1000", "<=500", "<>0". If the threshold lives in a cell, concatenate: ">" & E1.
3. 03
   Empty match returns #DIV/0!

   If zero rows match the criteria, AVERAGEIF divides by zero and returns #DIV/0!. The report breaks visibly in front of a stakeholder.

   Fix: Wrap with IFERROR for production reports: =IFERROR(AVERAGEIF(...), 0) or =IFERROR(AVERAGEIF(...), "No data"). For lookups specifically, prefer IFNA so genuine bugs still surface.
4. 04
   Treating zeros as missing data

   AVERAGEIF includes zero values in its calculation. A column with $0 entries for products that didn't sell that week pulls the average down. Sometimes that's correct (true mean), sometimes it's not (you wanted sales-day average).

   Fix: Decide what zero means in your data. If zero means "no sale that week" and you want the per-active-week mean, add a second criterion: AVERAGEIFS(..., range, "<>0"). Excludes the zeros from the divisor.

Notes

• Available in every Excel version since 2007. AVERAGEIFS arrived in 2007 too.
• Text criteria are case-insensitive. "North" and "north" match the same rows.
• Wildcards: * matches any string, ? matches one character. Use ~* or ~? to match a literal asterisk or question mark.
• Empty cells in the average_range are skipped. They don't pull the mean toward zero.
• Zero values ARE included. If you want to exclude zeros, add a "<>0" criterion via AVERAGEIFS.
• Empty matches return #DIV/0!. Wrap with IFERROR for production reports.
• AVERAGEIFS argument order: average_range FIRST, then range/criteria pairs. Different from AVERAGEIF.
• For weighted averages (different weights per row), AVERAGEIF won't do it. Use SUMPRODUCT instead.

What to learn next

Now prove it