Use AVERAGE to find the mean of numeric values in a range; use AVERAGEIF to average only values that meet one condition. For example, =AVERAGE(B2:B7) averages the numbers in B2:B7, while =AVERAGEIF(A2:A7,"North",B2:B7) averages the values in B2:B7 only where the corresponding cell in A2:A7 says North.
At a glance: the main difference
Both functions calculate an arithmetic mean: they add the eligible numbers and divide by how many eligible numbers there are. The difference is how they decide which numbers are eligible. AVERAGE takes numeric values from the arguments you provide. AVERAGEIF first checks a range against a condition, then averages the corresponding values—or the checked range itself if you do not provide a separate average range.
This distinction matters when a worksheet mixes categories, dates, statuses, or thresholds. If you want the overall average of test scores, AVERAGE is usually the direct choice. If you want the average score for one class, only completed orders, or sales above a cutoff, AVERAGEIF can do the filtering inside the formula.
- Use AVERAGE when every numeric value in the selected range should count.
- Use AVERAGEIF when one condition determines which values count.
- Use AVERAGEIFS when values must meet two or more conditions.
How AVERAGE works
AVERAGE has a short, flexible argument pattern. With a range in B2:B6, enter =AVERAGE(B2:B6). The function adds the numeric values it finds in that referenced range and divides by their count. A blank cell is not treated as zero, but a cell containing the number 0 is included and can lower the result.
For a concrete example, suppose B2:B6 contains 80, 90, a blank, the text n/a, and 100. AVERAGE returns 90 because it calculates (80 + 90 + 100) / 3. The blank and text entry are ignored in a cell reference. This is useful for incomplete records, but it also means that a missing value does not lower the average as a zero would. If missing results should count as zero, the data or formula needs to represent them as zeros deliberately.
AVERAGE can take more than one range or individual numeric argument, such as =AVERAGE(B2:B6,D2:D6). When arguments are cell references or ranges, text and logical values within those cells are ignored; error values can cause an error result. A number typed directly as an argument is different: directly supplied numbers count, and directly supplied logical values or text representations of numbers may also be evaluated. For typical worksheets, using cell ranges keeps the intended data easier to see and maintain.
How AVERAGEIF works
AVERAGEIF uses the pattern =AVERAGEIF(range,criteria,[average_range]). The required range is where Excel checks the condition, and criteria describes what must be true. The optional average_range identifies the numbers to average. If you omit average_range, Excel checks and averages the same range.
Suppose A2:A7 contains North, South, North, South, North, and East, while B2:B7 contains 120, 80, 150, 100, a blank, and 200. With those values in place, =AVERAGEIF(A2:A7,"North",B2:B7) returns 135: the two numeric sales values for North are 120 and 150, and (120 + 150) / 2 = 135. The North row with a blank sales cell does not add a numeric value to the average.
The ranges should normally have the same shape and cover corresponding rows. If average_range is a different size, Excel uses its upper-left cell as the starting point and averages cells corresponding to the criteria range. That can produce a valid-looking but misaligned result, so matching ranges such as A2:A100 and B2:B100 are safer and clearer.
Worked examples for common conditions
AVERAGEIF can use numeric comparisons as well as exact text matches. If the sales figures are in B2:B7, with 120, 80, 150, 100, a blank, and 200, the formula =AVERAGEIF(B2:B7,">100") returns 156.67 when displayed to two decimal places. Excel selects 120, 150, and 200; their total is 470, and 470 / 3 is 156.666…, which rounds to 156.67. Because no average_range is supplied, the condition range is also the range being averaged.
Criteria can also refer to a cell, which makes it easier to change a condition without editing the formula. If A2:A7 contains the categories in the previous example, B2:B7 contains their sales, and D2 contains North, then =AVERAGEIF(A2:A7,D2,B2:B7) returns 135. The formula uses D2’s value as the criterion, so changing D2 to South recalculates the result for South.
For a date-based average, dates must be real Excel dates rather than text that only looks like a date. If order dates are in A2:A10, amounts are in B2:B10, and D1 contains a real start date, =AVERAGEIF(A2:A10,">="&D1,B2:B10) averages amounts dated on or after D1. The comparison operator is joined to the date in D1 with an ampersand. This is more adaptable than typing a date directly into the criteria text, and it respects the date value stored in the cell.
Writing criteria correctly
For text criteria, put the exact text in quotation marks in the formula. Excel does not distinguish uppercase from lowercase in ordinary AVERAGEIF text matching, so North can match NORTH. Extra spaces can still matter: North and North followed by a space are not necessarily the same entry. If a match unexpectedly returns no values, check for inconsistent spelling and leading or trailing spaces.
Comparison criteria combine an operator with a value, such as >100, <=50, or <>0. When the operator and the value are typed directly into a formula, enclose the whole criterion in quotation marks. When the value comes from a cell, join the operator and cell reference with &, as in the date example above. A criterion such as <> checks for values not equal to the specified value; it does not automatically mean that every corresponding average cell contains a usable number.
Text criteria can use wildcards: an asterisk (*) matches any sequence of characters, and a question mark (?) matches one character. For example, a criterion of North* can match text beginning with North. Use a tilde (~) before a wildcard character when you want to search for the literal asterisk or question mark rather than use it as a wildcard. Wildcards are for text matching, not a general way to test numeric ranges.
Choosing between AVERAGE, AVERAGEIF, and AVERAGEIFS
Choose the simplest function that expresses the question. AVERAGE is easier to audit when all numeric entries should be treated alike. AVERAGEIF is a good fit for one rule, such as a region, product, date threshold, or value greater than a cutoff. It avoids building a separate filtered list just to compute a conditional average.
When the average must satisfy multiple rules, use AVERAGEIFS. For example, if A2:A7 contains regions and B2:B7 contains sales, =AVERAGEIFS(B2:B7,A2:A7,"North",B2:B7,">130") returns 150 for the sample data: the North entries are 120, 150, and blank, and only 150 is both numeric and greater than 130. AVERAGEIFS places the average range first, followed by pairs of criteria ranges and criteria. That argument order differs from AVERAGEIF, where the condition range comes first.
A useful decision check is to state the intended calculation in plain language before writing the formula:
- “Average every numeric entry in this range” points to AVERAGE.
- “Average the amounts for rows matching this one rule” points to AVERAGEIF.
- “Average the amounts for rows matching several rules” points to AVERAGEIFS.
Common problems and how to fix them
#DIV/0! means Excel has no numeric values to average after applying the function’s rules. With AVERAGEIF, that can happen when no cells meet the criterion, or when matching rows have no numeric entries in average_range. Check the criterion for spelling, spaces, comparison operators, and the actual data types. If an empty result is expected and should display differently, an error-handling formula may be appropriate, but do not hide the error until you have confirmed that the missing average is genuinely expected.
A result that seems too low often comes from zeros. AVERAGE includes numeric zero, and AVERAGEIF also includes zero values in the average range whenever their rows satisfy the criterion. Blanks and text in referenced cells are normally ignored, but zero is a number. Conversely, a result that seems too high can happen when blank or unavailable records were expected to count as zero but were left empty.
Another common issue is averaging the wrong column. In AVERAGEIF, range is the condition range; average_range is the values to average. If those arguments are reversed, Excel may still calculate something, but it will not answer the intended question. Confirm that each criteria cell lines up with the value in the same row. Also check whether numbers were imported as text: text-formatted numeric-looking entries may not be averaged as expected until they are converted to numbers.
Finally, formulas shown here use commas between arguments, as is standard in many English-language Excel installations. Some regional settings use semicolons as the formula argument separator. That is a regional formula-list setting, not a difference between AVERAGE and AVERAGEIF; use the separator Excel expects in your installation.
Frequently Asked Questions
Does AVERAGEIF ignore blank cells?
Blank cells in the average range do not contribute numeric values to the mean. However, a row can still meet the criterion while its corresponding average cell is blank; that blank does not become a zero. Numeric zeros are included.
Can AVERAGEIF use more than one condition?
AVERAGEIF handles one condition. For multiple conditions, use AVERAGEIFS, which accepts multiple criteria-range and criteria pairs. If you use AVERAGEIFS, remember that its first argument is the range to average.
Is AVERAGEIF case-sensitive?
No. Standard text matching in AVERAGEIF is not case-sensitive, so capitalization differences alone do not separate matches. Differences in spaces or other characters can still prevent a match.
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF applies one condition, while AVERAGEIFS can apply several conditions at once. Their argument order also differs: AVERAGEIF starts with the condition range; AVERAGEIFS starts with the average range.
Why does AVERAGEIF return #DIV/0!?
Usually, no qualifying numeric values were found. There may be no matching rows, or matching rows may have blanks or text rather than numbers in the average range. Review the condition and the data types before deciding whether to handle the error.
Practical takeaway
Think of AVERAGE as the direct mean of numeric cells and AVERAGEIF as that same mean after a one-condition selection. Use corresponding, same-sized ranges, make sure the average range contains numbers, and verify whether zeros should count. For several rules, move to AVERAGEIFS. Those checks make the formula easier to understand and help explain why its result may differ from a simple average of the whole column.
Related
- Excel: Merge Data From Multiple Cells Into One Cell
- Excel Merge Cells Option Not Available? Causes and Fixes
- Excel Merge Cells in Each Row Without Losing Values
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel NOW Function Not Updating? Causes and FixesExcel’s NOW() function does update, but it does not run like a live clock: it returns the current…
- Excel Day Countdown: Build a Live Days-Left FormulaWith a deadline date in B2, enter =B2-TODAY() in C2 to show how many calendar days remain. For…
- Excel Time Sheets for Employees: Setup, Formulas and TipsYes. Excel can track employee start times, finish times, unpaid breaks, and hours worked in a reusable time…
- How to Sort an Excel Table by One Column Without ErrorsTo sort an Excel table by one column, click the arrow in that column’s header and choose an…
- Excel Cell Fill Shortcut: Fill Down, Fill Right, and ColorIn Excel for Microsoft 365 on Windows, press Ctrl+D to fill down from the top cell of a…
- Combine Cells in Excel for Names: Formulas and StepsTo combine a first name in A2 and a last name in B2, enter =A2&" "&B2 in C2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.