AVERAGEIF handles one condition. Most real datasets, however, require several at once. You might need the average sales only for the East region in Q2 — two conditions simultaneously. The AVERAGEIFS function solves this directly. It averages a range of numbers against up to 127 separate conditions and returns a single result. All conditions must be met at the same time — AVERAGEIFS uses AND logic, not OR.
Consequently, AVERAGEIFS is the go-to function for conditional analysis in sales reports, HR data, quality control, and financial dashboards. Furthermore, it accepts text, numbers, dates, wildcards, and cell references as criteria — making it flexible for almost any filtering task.
What Is the AVERAGEIFS Syntax?
AVERAGEIFS follows the same pattern as SUMIFS and COUNTIFS — one average range, then criteria in range/criteria pairs.
| Argument | Required? | What it does |
|---|---|---|
| average_range | Required | The range of numbers to average. Empty cells, text, and TRUE/FALSE values in this range are ignored. Zero values are included. |
| criteria_range1 | Required | The first range to evaluate. Must be the same size and shape as average_range. |
| criteria1 | Required | The condition to apply to criteria_range1. Can be text, a number, a date, a logical expression in quotes, or a cell reference. |
| criteria_range2, criteria2... | Optional | Additional range/criteria pairs. Up to 127 pairs are allowed. All conditions must be TRUE for a row to be included in the average. |
How to Write Criteria in AVERAGEIFS
Criteria can be written in several ways. Text and exact-match numbers are the simplest. Operators like greater-than or less-than must be enclosed in double quotes. When the threshold comes from another cell, concatenate the operator string with the cell reference using &.
| Criteria goal | Example syntax | Meaning |
|---|---|---|
| Exact text match | "East" | Region equals "East" (case-insensitive) |
| Exact number match | 100 | Value equals exactly 100 |
| Greater than | ">500" | Value is above 500 |
| Less than or equal to | "<=100" | Value is 100 or below |
| Not equal to text | "<>Cancelled" | Value is anything except "Cancelled" |
| Cell reference threshold | ">="&D2 | Value is greater than or equal to whatever is in D2 |
| Wildcard — starts with | "North*" | Text begins with "North" (any suffix) |
| Wildcard — contains | "*Pro*" | Text contains "Pro" anywhere |
| Not blank | "<>" | Cell is not empty |
| Date before (from cell) | "<"&E2 | Date is earlier than the date in E2 |
Example 1: Two Text Criteria
Two-condition filtering is the most common use of AVERAGEIFS. A typical scenario is averaging sales for a specific product in a specific region. Both conditions must be met — neither alone is sufficient for a row to be included.
Example 2: Numeric Operator Criteria
Operators like greater than, less than, and not equal to allow you to filter rows by numeric thresholds. Enclose every operator inside double quotes. When the threshold comes from a cell reference, join the operator string with the reference using &.
">=G1" compares against the literal text "G1" — almost certainly wrong. Instead, write ">="&G1. Excel evaluates G1 first, then joins the result to the operator string.
Example 3: Date Range Criteria
AVERAGEIFS works naturally with dates. A between-dates filter requires two conditions on the same date column — one for the start boundary and one for the end. Both use the concatenation pattern for operators and cell references.
Example 4: Wildcard Criteria for Partial Text Matching
AVERAGEIFS supports the same wildcard characters as COUNTIFS and SUMIFS. An asterisk (*) matches any number of characters. A question mark (?) matches exactly one character. Wildcards are particularly useful for category columns where entries may carry varying suffixes or prefixes.
"~*" matches a real asterisk character.
Example 5: AVERAGEIF vs AVERAGEIFS — When to Use Each
AVERAGEIF handles one condition and uses a different argument order. AVERAGEIFS handles one or more conditions and always places average_range first. For new formulas, AVERAGEIFS is generally the better choice — the consistent argument order reduces mistakes when adding more criteria later.
The Key Argument Order Difference
AVERAGEIF places average_range last when three arguments are provided. AVERAGEIFS, by contrast, always places it first. This inconsistency catches many users off guard — particularly when migrating a formula from one function to the other.
Example 6: OR Logic Workaround
AVERAGEIFS uses AND logic only. There is no built-in OR mode. To average rows where condition A OR condition B is met, calculate each group separately and combine them. The SUMIFS and COUNTIFS approach avoids double-counting when rows could match both conditions.
Why Not Average Two AVERAGEIFS Results?
Simply averaging two AVERAGEIFS results gives the wrong answer when group sizes differ. For instance, three East sales and ten West sales would produce an incorrect overall average if you just average the two group results. SUMIFS divided by COUNTIFS handles unequal group sizes correctly.
How to Fix Common AVERAGEIFS Errors
#DIV/0! — no rows matched all conditions
AVERAGEIFS returns #DIV/0! when no rows satisfy every condition simultaneously. There is nothing to average, so no result is possible. Check each criterion individually to find which one excludes all rows. Wrapping in IFERROR is good practice for dashboards: =IFERROR(AVERAGEIFS(...), "No data").
#VALUE! — mismatched range sizes
Every criteria_range must be exactly the same size as average_range. For example, if average_range is C2:C100 (99 rows), each criteria_range must also cover 99 rows. A common mistake is accidentally including the header row in one range but not another.
Formula returns zero or a wrong number
Two likely causes exist. First, the average_range may contain numbers stored as text — use VALUE() to convert them. Second, a numeric criterion may be written as text without concatenation, such as ">=G1" instead of ">="&G1". Additionally, check whether dates in criteria columns are genuine dates by running ISNUMBER on a sample cell.
"<>0" on the average_range column to exclude them.
Frequently Asked Questions
-
What is the difference between AVERAGEIF and AVERAGEIFS?AVERAGEIF handles exactly one condition and uses the argument order (criteria_range, criteria, average_range). AVERAGEIFS handles one or more conditions and uses the order (average_range, criteria_range1, criteria1, ...). The key difference is that average_range comes first in AVERAGEIFS and last in AVERAGEIF. For new formulas, AVERAGEIFS is the better choice even with a single condition — its argument order stays consistent as conditions are added.
-
How do I use a cell reference as a criterion in AVERAGEIFS?For exact matches, use the cell reference directly: =AVERAGEIFS(C:C, B:B, G1). For comparisons with operators, concatenate the operator string with the reference using &: =AVERAGEIFS(C:C, B:B, ">="&G1). This is necessary because Excel must evaluate G1 to get its value before joining it with the operator. Writing ">=G1" would compare against the literal text string "G1" rather than the value in the cell.
-
Why does AVERAGEIFS return #DIV/0!?AVERAGEIFS returns #DIV/0! when no rows satisfy all conditions simultaneously. Check each criterion individually to find which one excludes all data. Common causes are a typo in a text criterion, a date stored as text, or a numeric threshold that no value meets. AVERAGEIFS is case-insensitive but not whitespace-insensitive — a trailing space in a cell value can cause a mismatch. Wrap the formula in IFERROR to return a friendly message instead of the error.
More Questions About AVERAGEIFS
-
Can AVERAGEIFS use OR logic?Not directly — AVERAGEIFS always uses AND logic. For OR logic, calculate each group separately with SUMIFS and COUNTIFS, then divide: =(SUMIFS(C:C,B:B,"East")+SUMIFS(C:C,B:B,"West"))/(COUNTIFS(B:B,"East")+COUNTIFS(B:B,"West")). In Excel 365, AVERAGE(FILTER(C2:C100,(B2:B100="East")+(B2:B100="West"))) is a cleaner alternative. The + between Boolean arrays creates OR logic for the FILTER function.
-
Do wildcards work in AVERAGEIFS?Yes. AVERAGEIFS supports the asterisk (*) for any sequence of characters and the question mark (?) for exactly one character. For example, "North*" matches "North", "NorthEast", and "NorthWest". To combine a wildcard with a cell reference, concatenate: G1&"*" where G1 holds the prefix text. To match a literal asterisk or question mark in the data, precede it with a tilde character: "~*" matches a real asterisk.
-
Which Excel versions support AVERAGEIFS?AVERAGEIFS is available in Excel 2007 and all later versions, including Microsoft 365, Excel 2024, 2021, 2019, and 2016. It also works in Excel for the web and Google Sheets. The older AVERAGEIF function is available from Excel 2003 onwards and handles single-condition averaging only.