Excel AVERAGEIF vs AVERAGEIFS: Rules and Examples

Formulas & functions · Updated November 19, 2026

Use AVERAGEIF when you need to average values that meet one condition; use AVERAGEIFS when every result must meet two or more conditions. Both functions calculate an arithmetic mean, but they take their arguments in a different order, so choosing the right one—and arranging its ranges correctly—matters.

The core difference

AVERAGEIF checks one range against one criterion, then averages the matching values. For example, you can average sales for one region, scores above a threshold, or expenses in a particular category. Its limitation is that it can apply only one condition in a single function call.

AVERAGEIFS can apply several conditions at once. A row is included only when it passes all the conditions: this is AND logic. You might use it to average sales for the East region in January, or average test scores for one class where attendance is at least 90%. You can provide up to 127 criteria-range and criterion pairs.

  • Choose AVERAGEIF for one condition.
  • Choose AVERAGEIFS for two or more conditions that must all be true.
  • For a single condition, either function can often do the job, but their argument order differs.

How the arguments work

In AVERAGEIF, the first argument is the range Excel checks, the second is the criterion, and the optional third argument is the range Excel averages. If you leave out the average range, Excel checks and averages the first range. This is convenient when the condition and the numbers to average are in the same cells—for example, averaging values in a range that are greater than zero.

In AVERAGEIFS, the average range comes first. After it, provide a criteria range and a criterion, then add another range-and-criterion pair for each additional condition. That reversal catches many people moving from one function to the other: the values to average are not the first argument of AVERAGEIF, but they are the first argument of AVERAGEIFS.

Keep the ranges aligned: the average range and each criteria range should cover corresponding rows and columns. If one range starts a row earlier than another, Excel can compare a condition from one record with a value from a different record. That may return a number without an obvious error, making alignment worth checking whenever a result looks surprising.

A worked example with sales

Suppose A2:A7 contains the region for each sale and B2:B7 contains the sales amounts. The regions are East, East, West, East, West, and East; the corresponding amounts are 120, 80, 200, 150, 90, and 100. To average all sales in the East, use this formula:

With regions in A2:A7 and sales in B2:B7, this formula returns 112.5: =AVERAGEIF(A2:A7,"East",B2:B7)

The matching amounts are 120, 80, 150, and 100. Their sum is 450, and 450 divided by four sales is 112.5. Notice that the condition range is the first argument and the sales range is the third.

Now suppose you want only East sales of at least 100. A single condition is no longer enough; both the region and the amount must qualify. With the same data layout, this formula returns about 123.33:

With regions in A2:A7 and sales in B2:B7, this formula averages the qualifying East sales: =AVERAGEIFS(B2:B7,A2:A7,"East",B2:B7,">=100")

The included sales are 120, 150, and 100. Their total is 370, and 370 divided by three is 123.333… . The formula puts B2:B7 first because it is the range being averaged, then pairs each criteria range with its condition.

Criteria: text, numbers, dates, and wildcards

Criteria can be exact text, a number, a comparison, or a cell reference. For comparisons such as greater than, less than, or not equal to, put the operator and value together inside quotation marks. If the comparison value is stored in a cell, join the operator to that reference with the ampersand. Text criteria need quotation marks when typed directly into a formula; a criterion held in a cell is referenced without adding quotation marks around the cell reference.

Dates are often easiest to handle with a start boundary and an exclusive end boundary. Suppose A2:A5 contains January 5, January 20, February 1, and January 31, 2026, while B2:B5 contains 100, 200, 300, and 400. To average January values, use the start of January and the start of February as the two limits. With dates in A2:A5 and amounts in B2:B5, this formula returns about 233.33:

=AVERAGEIFS(B2:B5,A2:A5,">="&DATE(2026,1,1),A2:A5,"<"&DATE(2026,2,1))

Only 100, 200, and 400 qualify; their average is 700 divided by three. The less-than-February boundary includes every time on January 31, even if the date cells also contain times. A criterion such as “through January 31” can miss entries with a time component if it is treated as a comparison to midnight on that date.

For text criteria, the asterisk wildcard represents any number of characters and the question mark represents one character. For example, a criterion beginning with a department name followed by an asterisk can match longer labels with that beginning. Use a tilde before a wildcard character when you want to match the literal asterisk or question mark rather than use it as a wildcard.

Choosing the right function

Start by stating the rule for including a row in plain language. If it has one test—“region is East”—AVERAGEIF is a straightforward fit. If it has multiple tests—“region is East and sale is at least 100”—use AVERAGEIFS. More criteria do not mean you need to nest one averaging function inside another; add another criteria-range and criterion pair to AVERAGEIFS.

For one condition, you may choose either function. AVERAGEIF expresses that case directly, while AVERAGEIFS can be useful if you expect the report to gain more filters later. Whichever you use, make the formula understandable to the next person: keep the relevant data in clearly labeled columns and, where appropriate, put criteria in cells rather than burying frequently changed values in a long formula.

Neither function handles OR logic in the same way that AVERAGEIFS handles AND logic. If you want East or West, adding both region conditions to one AVERAGEIFS would require a row to be both East and West at once. Instead, use a helper column that labels rows meeting either condition, or calculate a combined total and count for the qualifying groups. Do not simply average the two group averages unless the groups have the same number of numeric values; otherwise, that gives each group equal weight rather than each record equal weight.

Common problems and how to fix them

  • Wrong argument order: Check whether the formula is AVERAGEIF or AVERAGEIFS. In the latter, the average range must come first.
  • No qualifying numeric values: Excel returns #DIV/0! when no numeric cells are available to average, including when no rows match the criteria. Check the criterion spelling, comparison operator, and data type.
  • Numbers stored as text: A cell that looks like a number may not be treated as a numeric value. Check for imported text, leading apostrophes, or inconsistent formatting, and convert the source data if needed.
  • Dates do not match: Excel dates are stored as serial numbers, so text that looks like a date may not compare as a real date. Check that the cells contain valid dates and that the criterion boundaries cover the intended period.
  • Ranges are misaligned: Make sure each criteria range corresponds row by row with the average range. Use the same start and end rows for a typical column-based calculation.
  • Unexpected blank handling: Blank cells in the average range do not contribute numeric values to the mean. A criterion that targets blanks is different from a criterion that targets zero; blank and zero are not interchangeable.

Both functions can also return an error if an error value in the data prevents the calculation. Filtering or correcting the source errors may be more useful than hiding the symptom, especially when the average informs a report. If you need a friendlier display for a no-match case, handle the result separately and make sure readers can distinguish “no qualifying data” from a genuine average of zero.

Related options and practical notes

If your goal is to count matching records rather than average them, use a counting function such as COUNTIF for one condition or COUNTIFS for several. If you want to add matching values, use SUMIF or SUMIFS. These related functions follow similar criteria ideas, but their purpose is different: an average is a total divided by the count of numeric values, not a sum or a record count.

For a more complex report, a PivotTable can summarize values by categories and let you change the layout interactively. A formula may be easier when the result needs to appear in a specific cell or feed another calculation; a PivotTable may be more convenient when readers need to explore several groupings. Excel for Microsoft 365 on Windows supports both functions, and their formulas work the same way in current Excel for Mac. Argument separators can vary with regional settings; if Excel rejects a formula copied from another locale, check whether your installation expects a different list separator.

Frequently Asked Questions

Can AVERAGEIF handle more than one condition?
Not in a single AVERAGEIF call. Use AVERAGEIFS to apply multiple conditions together, or create a helper column if the rule is more complicated.

Does AVERAGEIFS use AND or OR?
It uses AND logic: a row must satisfy every supplied condition to qualify. For OR logic, use a helper column or combine the qualifying totals and counts carefully.

Can I use AVERAGEIFS with just one condition?
Yes. It accepts one criteria-range and criterion pair. For that simple case, AVERAGEIF is also an option; remember that the argument order differs.

Why does AVERAGEIF return #DIV/0!?
Usually, no cells met the criterion, or the matching cells contained no numeric values to average. Check the source data and the exact criterion, including whether numbers or dates are stored as text.

Are AVERAGEIF and AVERAGEIFS available in Excel for Mac?
Yes. Both functions are available in current Excel for Mac, and their argument logic is the same. Regional settings may affect which character Excel uses to separate formula arguments.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.