AVERAGEIF is not a number in Excel; it is a worksheet function that calculates the average of cells meeting one condition, and its result is a number. With region names in A2:A7 and sales amounts in B2:B7, enter =AVERAGEIF(A2:A7,"East",B2:B7); if East sales are 120, 180, and 150, the result is 150.
What AVERAGEIF Means in Excel
AVERAGEIF combines two ideas: it checks a range for a specified condition, then averages the corresponding values that qualify. It is useful when you want a mean for one category or threshold without first filtering or copying the matching rows. For example, it can find average sales for one region, average scores above a pass mark, or average costs for a particular type of expense.
The answer is a numeric value, not a label or a count. Excel may display that value as a decimal, currency, percentage, date, or another format depending on the cell’s number format. Formatting changes how a value appears, not the underlying calculation. If the result looks like a date or has unexpected decimal places, check the cell’s number format before assuming the function returned the wrong kind of value.
AVERAGEIF averages numbers; it does not add all matching values or divide by every matching row regardless of content. Blank cells and text in the cells being averaged do not contribute to the average. This distinction matters when a matching row has a blank amount, a note such as “pending,” or a number stored as text.
How the Function’s Arguments Work
AVERAGEIF has three arguments. The first is the range Excel checks for matches. The second is the criterion that defines a match. The third, which is optional, is the range containing the numbers to average. When you omit the third argument, Excel checks and averages the first range itself.
In the opening example, A2:A7 is the criteria range, “East” is the criterion, and B2:B7 is the average range. Excel examines each region label and averages the sales amount in the corresponding row whenever the label is East. Keep the criteria range and average range aligned row by row so each condition applies to the intended value.
If the two ranges have different dimensions, Excel uses the top-left cell of the average range as its starting point and then considers cells corresponding to the criteria range’s shape. That behavior can be surprising, so using equally sized ranges is the clearest and safest practice. For a dataset that grows, using a table or extending both ranges consistently helps prevent rows from being left out.
How Excel Applies a Criterion
A criterion can be a text value, a number, a comparison, or a pattern. Text criteria are generally enclosed in quotation marks inside the formula. Comparison criteria use operators such as greater than, less than, or equal to. When an operator is part of the criterion, put the operator and value together in a text criterion, such as “>=80”.
With scores in D2:D7 of 68, 82, 91, 74, 88, and 59, this formula averages the scores that are at least 80. The qualifying values are 82, 91, and 88, so the result is 87.
With those scores in D2:D7, enter =AVERAGEIF(D2:D7,">=80") to get 87.
To make the threshold editable, put 80 in F1 and refer to that cell. With scores in D2:D7 and the cutoff in F1, =AVERAGEIF(D2:D7,">"&F1) averages scores strictly greater than 80; for the listed scores, it returns 87. The ampersand joins the comparison operator to the value in F1. Because 80 itself is not greater than 80, it would not be included if it appeared in the data.
For text patterns, the asterisk matches any number of characters and the question mark matches one character. A tilde can escape a wildcard when you want to match the literal character rather than use it as a pattern. This is handy for categories with consistent prefixes, but wildcard criteria should be used carefully if similar labels could be unintentionally included.
Useful Examples Beyond a Basic Average
For sales by region, use a text criterion such as East. For a score threshold, use a criterion such as “>=80”. The same function can filter on a date range boundary because Excel stores dates as serial numbers, though date criteria often need to be joined to a cell reference. A date displayed in a worksheet is still a numeric value underneath.
Suppose transaction dates are in A2:A100, amounts are in B2:B100, and F1 contains a real Excel date. With those cells laid out as described, =AVERAGEIF(A2:A100,">="&F1,B2:B100) returns the average amount on or after the date in F1. The result depends on the workbook’s transactions, so there is no single numeric answer for this layout. Make sure dates in the criteria range are genuine Excel dates rather than text that only looks like a date.
AVERAGEIF also works when the criterion is a text category but the values to average are elsewhere on the same rows. This is its most common pattern: one column contains labels and another contains measurements. It can also average the values within the checked range itself, as with the score example, because the average range is optional.
AVERAGEIF Compared With Related Functions
AVERAGE calculates the mean of numeric values without applying a condition. AVERAGEIF adds one condition, which makes it suitable when each row should qualify based on one test. For example, use it to average a single region or all amounts above a threshold.
AVERAGEIFS is the related choice when a row must satisfy multiple conditions, such as a region being East and a date being in a selected period. Its argument order differs: it starts with the average range, followed by pairs of criteria ranges and criteria. Do not simply add a second condition to AVERAGEIF; use AVERAGEIFS when more than one test must be met.
Filtering the table and using AVERAGE can be useful when you want to inspect the matching rows as well as calculate a result. AVERAGEIF is more direct for a repeatable summary, especially when the criterion is in a cell and can be changed. A PivotTable is another option for exploring averages across many categories, but it has a different setup and is more useful for interactive summaries than for one formula-driven result.
Common Errors and Unexpected Results
A frequent error is #DIV/0!. AVERAGEIF returns this when no cells meet the criterion or when the matching cells in the average range contain no numeric values to average. Check spelling, spaces, the criterion operator, and whether the qualifying rows actually contain numbers. If the result should be blank rather than an error when there are no matches, that requires an error-handling approach; first confirm the condition is correct rather than hiding a data problem.
Another common issue is numbers stored as text. A cell may look like 120 but be stored as text after an import or manual entry. Such entries are not treated as ordinary numeric values in the average. Check the source data and convert text-formatted numbers to numeric values if appropriate. Also distinguish a true blank from a zero: a zero is a number and affects the average, while a blank does not.
Check the criterion itself if a formula returns an unexpectedly high or low average. A criterion of “>80” excludes 80, while “>=80” includes it. Text labels can also contain leading or trailing spaces, and wildcard patterns may match more categories than intended. Finally, confirm that the criteria and average ranges begin on corresponding rows and cover the intended records.
Entering and Maintaining the Formula
In Microsoft Excel for Microsoft 365 on Windows, select the cell for the result, type the formula beginning with an equals sign, and press Enter. On a Mac, enter the same formula and press Return. The function and its basic syntax are the same in desktop Excel on both systems. If Excel uses a regional setting with semicolons as formula separators, its expected separator may differ from the comma examples here.
For recurring reports, keep source data in consistent columns, use clear headings, and avoid mixing numbers with explanatory text in the amount column. If new rows are added regularly, an Excel table can make a report easier to maintain because formulas and structured data expand as the table grows. Whatever range style you choose, verify that newly added records are included in both the criteria and average ranges.
When a result seems wrong, test the logic in a small sample: identify the rows that should qualify, list their numeric values, and calculate their mean. This simple check helps separate a formula syntax issue from a data-quality issue. It is also useful to format the result cell deliberately, for example as Number with an appropriate number of decimal places, so the displayed answer is easy to interpret.
Frequently Asked Questions
Does AVERAGEIF return a number?
Yes. It returns the arithmetic average as a numeric value when it has qualifying numeric values to average. The cell’s formatting can make that number appear as currency, a date, or another display format.
Is AVERAGEIF the same as AVERAGE?
No. AVERAGE calculates a mean without a condition. AVERAGEIF calculates a mean only for values associated with cells that meet one condition.
Why does AVERAGEIF show #DIV/0!?
Usually, no cell meets the criterion, or the matching cells in the average range contain no numbers. Review the condition, the source values, and whether numeric-looking entries are actually stored as numbers.
Can AVERAGEIF use more than one condition?
AVERAGEIF handles one condition. Use AVERAGEIFS when the average should include only rows that meet multiple conditions.
What happens if I leave out the average range?
Excel averages the cells in the criteria range that meet the condition. Include a separate average range when the condition is in one column and the values to average are in another.
Related
- Excel Merge Cells in Each Row Without Losing Values
- Excel Merge Cells Greyed Out in a Table? Here’s Why
- Excel Merge Cells Across Rows: Methods, Limits, Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Week of the Year: WEEKNUM vs. ISO WeeksTo get a week number from a date in Excel, use =WEEKNUM(A2,2) for weeks that start on Monday,…
- Excel LAMBDA in Name Manager: How to Create and Use ItYes. In Microsoft 365 Excel for Windows, you can save a LAMBDA formula as a defined name in…
- XLOOKUP Match Mode: Exact, Approximate, and WildcardsIn Excel, XLOOKUP’s match_mode is its fifth argument: use 0 for an exact match, -1 for an exact…
- Excel Time Difference in Hours: Formulas and ExamplesTo calculate the difference between two Excel times in decimal hours, subtract the start time from the end…
- COUNTIF Not Including Zero? Excel Formula ExamplesIn Excel for Microsoft 365, use =COUNTIF(A2:A20,"<>0") to count cells whose values are not zero, assuming your data…
- Count Excel Rows After a Filter: Formulas That WorkTo count rows currently visible after filtering, use SUBTOTAL on a column that has a value in every…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.