In Microsoft Excel, use =AVERAGEIF(range,criteria,average_range) to average values that meet one condition. For example, if categories are in A2:A7 and amounts are in B2:B7, =AVERAGEIF(A2:A7,"North",B2:B7) averages the amounts on rows labeled North.
What AVERAGEIF does
AVERAGEIF checks one range against one condition, then calculates the arithmetic mean of the corresponding qualifying numbers. It is useful when a table contains categories or measurements and you want an average for just one group: average sales for a region, hours for a project, or scores above a threshold. Unlike filtering the table manually, the formula updates its result when the source values change.
The function has three arguments, in this order: the range Excel evaluates, the condition (called the criteria), and the range containing the values to average. The third argument is optional. If you leave it out, Excel checks and averages cells in the same range. AVERAGEIF is not case-sensitive for text criteria, so North and NORTH are treated alike.
Only numeric values in the cells being averaged contribute to the result. Text and empty cells are ignored; a numeric zero is included. That distinction matters: a zero can lower an average, while a blank does not count as a value.
How the arguments fit together
Think of the first range as the test column and the average range as the result column. Excel tests each row in the first range; when a row meets the condition, it includes the corresponding cell from the average range in the calculation. Keep the two ranges the same size and aligned to the same rows. For example, testing A2:A100 and averaging B2:B100 pairs the category in A2 with the value in B2, then A3 with B3, and so on.
The criteria can be a fixed value, a comparison, a wildcard pattern, or a reference to another cell. Text criteria and comparison expressions are usually enclosed in straight double quotes. A cell reference used as the criterion is not enclosed in quotes. When a comparison operator needs to be combined with a cell reference, join them with the ampersand (&).
For a compact list where the values to test are also the values to average, the third argument can be omitted. With scores in C2:C30, for example, the condition can select which scores from that same range contribute. If the test and average columns differ, include both ranges so Excel does not average the category labels or some unrelated field.
A basic category example
Suppose A2:A7 contains North, South, North, East, North, and South, while B2:B7 contains 12, 10, 20, 15, 16, and 14. With those category labels in A2:A7 and amounts in B2:B7, this formula returns 16:
=AVERAGEIF(A2:A7,"North",B2:B7)
Excel selects the North rows and averages 12, 20, and 16. Their total is 48, and 48 divided by 3 is 16. The other rows are not included. If the labels are likely to change, put the desired label in E2; with categories in A2:A7, amounts in B2:B7, and the criterion in E2, use:
=AVERAGEIF(A2:A7,E2,B2:B7)
This makes the result easier to reuse: change E2 from North to South and the formula calculates the South average without editing the formula itself. In this example, that result is 12, because the South values are 10 and 14.
Using numbers, comparisons, and wildcards
Criteria can test numbers as well as labels. For example, to average values greater than 100 in a single numeric column, use a comparison criterion. With the numbers to test and average in B2:B20, the formula is:
=AVERAGEIF(B2:B20,">100")
Comparison operators include greater than (>), less than (<), equal to (=), and their inclusive forms (>= and <=). For an exact match, you can use a number directly as the criterion. To base a comparison on a value stored in a cell, join the operator to that reference. With amounts in B2:B20 and a cutoff in E2, this averages amounts greater than the cutoff:
=AVERAGEIF(B2:B20,">"&E2)
Text criteria can use wildcards. An asterisk (*) represents any sequence of characters, including no characters; a question mark (?) represents exactly one character. With product names in A2:A50 and unit prices in B2:B50, this formula averages prices for names containing the word tea:
=AVERAGEIF(A2:A50,"*tea*",B2:B50)
Wildcards are helpful when labels contain extra wording or codes, but they can also match more rows than intended. To find a literal asterisk or question mark in text, precede that wildcard with a tilde (~) in the criterion. Check the matching rows if the pattern is broad, especially when similar names should be treated separately.
Working with dates
Excel stores dates as serial numbers, so AVERAGEIF can compare date cells using the same operators used for numeric criteria. Use DATE(year,month,day) to build a date criterion reliably rather than typing a date string whose interpretation might vary with regional settings. With transaction dates in A2:A5 and revenue in B2:B5, the following formula averages revenue for dates on or after January 1, 2025:
=AVERAGEIF(A2:A5,">="&DATE(2025,1,1),B2:B5)
AVERAGEIF accepts only one condition, so it cannot by itself set both a start date and an end date. For a date window, use AVERAGEIFS, which supports multiple conditions. Suppose A2:A5 contains December 31, 2024; January 5, 2025; March 20, 2025; and January 2, 2026, while B2:B5 contains 50, 100, 200, and 300. With dates in A2:A5 and revenue in B2:B5, this formula returns 150, the average of 100 and 200:
=AVERAGEIFS(B2:B5,A2:A5,">="&DATE(2025,1,1),A2:A5,"<"&DATE(2026,1,1))
Notice that AVERAGEIFS puts the average range first, unlike AVERAGEIF. Using a less-than condition for the first day of the next year includes every date in 2025 without relying on a particular time of day.
AVERAGEIF compared with related functions
Choose the function that matches the number of conditions you need. AVERAGEIF handles one condition; AVERAGEIFS handles two or more conditions, such as a region plus a minimum sales amount. AVERAGE calculates a mean without applying a condition, while SUBTOTAL can calculate an average that responds to filtered or hidden rows depending on its function number. These functions solve different problems, so replacing one with another can change which records count.
AVERAGEIF is also different from SUMIF. SUMIF adds qualifying values; AVERAGEIF divides their total by the count of qualifying numeric values. If you need a weighted average, AVERAGEIF is generally not enough: a weighted calculation must account for each value’s weight, rather than treating every qualifying row equally. Also remember that AVERAGEIFS is not simply AVERAGEIF with extra criteria appended; its argument order starts with the range to average.
Common errors and how to fix them
If the formula returns an unexpected result, inspect the data and range alignment before changing the function. Common causes include:
- Wrong range pairing: The test range and average range start on different rows or cover different records. Align them so each tested row corresponds to the intended value.
- Numbers stored as text: A cell may look numeric but be stored as text, so it will not contribute as an average value. Check the cell format and underlying data, and convert imported text values to numbers if appropriate.
- Extra spaces or inconsistent labels: A label such as North followed by a space may not match the clean label North. Clean imported data or inspect the actual cell contents.
- No qualifying numeric values: If nothing matches, or the matching average-range cells contain no numbers, Excel cannot calculate a mean and may return #DIV/0!. Confirm that the criterion is spelled and formatted as intended and that qualifying rows contain numbers.
- Dates stored as text: A date-looking string may not behave as a real Excel date. Confirm the cells contain date values, not text imported from another system.
Another frequent misunderstanding is treating blank cells as zeros. AVERAGEIF ignores empty or text cells in its average range, but includes genuine numeric zeros. If missing data should count as zero in your analysis, make that choice explicit in the source data or calculation; do not assume the function will interpret a blank as zero.
Excel for Microsoft 365 on Windows uses commas between function arguments in the examples here. Some regional settings use semicolons as the list separator, so Excel may display or require that separator on a particular computer. The function and argument order are otherwise the same. The formulas also work in Excel for Mac and Excel for the web, although local separator settings can vary.
Practical tips for maintainable formulas
For a small fixed dataset, direct cell ranges are clear and easy to audit. If the list will grow, consider converting it into an Excel table using Insert > Table. Table references can expand as rows are added and make formulas easier to understand, though the precise structured-reference formula depends on the table and column names. Keep labels consistent, avoid including headings in the data ranges, and use a separate input cell for criteria that users may change.
When validating a result, filter or sort the source records by the condition and calculate the qualifying values independently. Check both the count of matching numeric cells and their sum: a mean is the sum divided by that count. This quick check is useful when a result seems plausible but may be missing rows due to spelling, date, or data-type differences.
Frequently Asked Questions
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF tests one condition. AVERAGEIFS can test multiple conditions at once and places the range to average first in its arguments. Use AVERAGEIFS when a request combines conditions such as region and date range.
Can AVERAGEIF use more than one condition?
No. AVERAGEIF has one criteria argument. Use AVERAGEIFS for multiple conditions rather than trying to combine separate tests inside AVERAGEIF.
Does AVERAGEIF count zero or blank cells?
A numeric zero in the average range is included in the mean. A blank cell and text in the average range are ignored. A blank in the criteria range is a different issue: criteria can be written to select blank or nonblank cells.
Why does my AVERAGEIF formula return #DIV/0!?
Excel returns this error when there are no qualifying numeric cells to average. Check that the criterion matches the data and that the corresponding cells in the average range contain numbers rather than blanks or text.
Can AVERAGEIF average dates?
Yes. Dates are stored as numbers, so the function can average qualifying date values. If you mean average a measurement for records within a date range, use AVERAGEIFS to apply both the start-date and end-date conditions.