AVERAGEIF Excel Formula: Examples, Criteria, and Tips

Formulas & functions · Updated November 18, 2026

In Excel, use AVERAGEIF to average numbers that meet one condition. For example, with departments in A2:A7 and sales amounts in B2:B7, enter =AVERAGEIF(A2:A7,"North",B2:B7) to average the sales for North; if those amounts are 120, 150, and 90, the result is 120.

What AVERAGEIF Does

AVERAGEIF checks a range of cells against a condition, then calculates the arithmetic mean of the corresponding values. It is useful when a worksheet contains categories alongside measurements—for example, regions and sales, project names and hours, or product types and prices. Rather than filtering rows and calculating a separate average, you can express the condition in a formula that updates when the source data changes.

The function has three arguments: the cells Excel checks, the condition those cells must satisfy, and the values to average for rows that qualify. The third argument is optional. If you leave it out, Excel checks and averages the same range. AVERAGEIF handles one condition; use AVERAGEIFS when a result must satisfy two or more conditions.

AVERAGEIF is available in Microsoft Excel for Microsoft 365 on Windows, as well as current Mac and web versions. Its basic syntax is =AVERAGEIF(range,criteria,[average_range]). Square brackets here indicate an optional argument; do not type those brackets into the formula.

A Basic AVERAGEIF Example

Suppose A2:A7 contains North, South, North, East, North, and South, while B2:B7 contains 120, 90, 150, 80, 90, and 110. To find the average sales for North, use the department range as the first argument, North as the condition, and the sales range as the third argument. With that layout, the formula is =AVERAGEIF(A2:A7,"North",B2:B7), which returns 120: (120 + 150 + 90) divided by 3.

The order of the arguments matters. The first range contains the values Excel tests, and the third range contains the values Excel averages. In this example, swapping the ranges would ask Excel to test sales against the word North, which is not the intended calculation. The corresponding cells in the two ranges should represent the same rows.

If you want to average values in the range being tested, you can omit the third argument. For instance, with scores in C2:C8, a condition of 80 or higher can be applied directly to those scores. With those scores in C2:C8, =AVERAGEIF(C2:C8,">=80") returns the average of the qualifying scores, not the average of every score in the range.

Writing Criteria for Text and Numbers

Criteria can be text, a number, a comparison, or a cell reference. Put text criteria in double quotation marks when typing them directly into a formula, as in the North example. A direct numeric criterion can be written without quotation marks, but comparison operators such as greater than or less than must be included in a text criterion. For example, with values in B2:B7, =AVERAGEIF(B2:B7,">100") averages values strictly greater than 100. For the sample amounts 120, 90, 150, 80, 90, and 110, that result is 126.67, from (120 + 150 + 110) divided by 3.

Common comparison operators are > (greater than), >= (greater than or equal to), < (less than), <= (less than or equal to), and <> (not equal to). To match a value other than North in A2:A7 and average its corresponding amounts in B2:B7, use =AVERAGEIF(A2:A7,"<>North",B2:B7). In an actual Excel formula, type the less-than and greater-than characters directly; the HTML display above represents them with character entities.

When a threshold is stored in a worksheet cell, join the operator to the cell reference with an ampersand. If E2 contains 100 and B2:B7 contains the sample amounts, =AVERAGEIF(B2:B7,">"&E2) returns 126.67. This approach lets you change the threshold in E2 without editing the formula. If E2 contains a date, the same pattern can be used with an appropriate date comparison, provided the worksheet cells contain real Excel dates rather than text that merely looks like a date.

Using Cell References and Wildcards

A cell reference is often easier to maintain than a criterion typed into the formula. If E2 contains North, and the department and sales data are in A2:A7 and B2:B7, respectively, =AVERAGEIF(A2:A7,E2,B2:B7) returns 120. You can copy the formula and change E2 to another department to calculate a different average. This is particularly handy for summary tables or reports whose selection changes regularly.

For text criteria, AVERAGEIF supports two wildcard characters. An asterisk (*) matches any number of characters, including none; a question mark (?) matches exactly one character. With region names in A2:A7 and corresponding sales in B2:B7, =AVERAGEIF(A2:A7,"N*",B2:B7) averages rows whose region begins with N. The match is not case-sensitive, so North and NORTH are treated alike.

To search for a literal asterisk or question mark rather than use it as a wildcard, put a tilde before the character in the criterion. For example, a tilde followed by an asterisk asks Excel to match an actual asterisk in the text. This is useful for unusual labels, but ordinary category names generally need no wildcard syntax.

When to Use AVERAGEIFS Instead

AVERAGEIF accepts one condition. If you need to average sales for North in the Retail channel, use AVERAGEIFS, which starts with the range to average and then takes pairs of criteria ranges and criteria. For example, if regions are in A2:A7, channels are in B2:B7, and sales are in C2:C7, =AVERAGEIFS(C2:C7,A2:A7,"North",B2:B7,"Retail") averages sales only where the region is North and the channel is Retail.

For a concrete check, suppose those six rows contain North/Retail/120, South/Retail/90, North/Online/150, North/Retail/80, South/Online/110, and North/Online/100. The formula returns 100 because the two rows meeting both conditions have sales of 120 and 80. AVERAGEIFS can add further criteria-range and criterion pairs when the analysis requires more conditions. Make sure each criteria range and the average range cover corresponding rows and have compatible dimensions.

Do not try to put two independent conditions into one AVERAGEIF criterion and expect it to behave like a multi-condition test. Choose AVERAGEIFS for multiple requirements, such as a date period plus a department, or a product plus a status.

Entering and Checking the Formula

On Excel for Microsoft 365 for Windows, select the cell where the result should appear, type the formula beginning with an equals sign, and press Enter. On a Mac, enter the formula in the same way and press Return. Excel for the web also accepts the same AVERAGEIF syntax. In the examples here, commas separate arguments, which is the standard syntax shown for the specified English-language Excel setup; some regional settings use semicolons as formula separators.

Before relying on the result, check the ranges and the condition. Confirm that the first range contains the categories or values being tested, and that the average range contains the corresponding numeric measurements. A quick manual check of the matching rows can catch a shifted range or an unexpected category spelling. Formatting a cell as a percentage or currency changes how a result appears, not the underlying calculation.

If a formula needs to be copied down a summary table, consider locking the source ranges with dollar signs so they do not shift as the formula moves. For example, if the fixed data ranges are A2:A100 and B2:B100 and each row’s criterion is in E2, the formula =AVERAGEIF($A$2:$A$100,E2,$B$2:$B$100) keeps the data ranges fixed while allowing the criterion reference to change when copied down.

Common Errors and Misunderstandings

  • #DIV/0!: Excel returns this error when no qualifying numeric values are available to average. The criterion may match no rows, or matching rows may have blank or nonnumeric cells in the average range. Check the condition and inspect the corresponding values.
  • Unexpected average: AVERAGEIF ignores text and blank cells in the average range, but numeric zeros are included. If a zero represents a real measurement, it affects the average; if it means “not recorded,” decide whether the data should be corrected or handled with a different formula.
  • Text that looks numeric: Values stored as text may not behave like numbers in an average. Check the source data for imported values, leading apostrophes, or inconsistent formats rather than assuming that display formatting converts text into numbers.
  • Incorrect matches: Text comparisons are not case-sensitive, and wildcard criteria can match more than one label. If the result includes unintended categories, use a more specific criterion or clean up inconsistent source labels.
  • Misaligned ranges: If the criteria range and average range refer to different row layouts, Excel may pair a condition with the wrong measurement. Keep them aligned and verify the first and last rows in each reference.

AVERAGEIF does not treat an empty criterion as a request to average every row. A blank criterion can match blank cells in the criteria range, which is different from calculating the overall average. For an unconditional average, use AVERAGE on the numeric range instead.

AVERAGEIF Compared with AVERAGE

AVERAGE calculates the mean of its numeric arguments without applying a condition. AVERAGEIF adds a test, so it is the better choice when the question is “What is the average for this group?” For the North example, AVERAGE of B2:B7 would include all six amounts, while AVERAGEIF includes only the amounts paired with North. Use AVERAGEIFS when the group is defined by multiple conditions.

These functions calculate an arithmetic mean, not a median or a weighted average. AVERAGEIF gives each qualifying numeric value equal weight. If each row represents a different quantity and the question requires weighting, a simple AVERAGEIF result may not answer the intended question; the calculation needs to reflect the actual weighting method and data structure.

Frequently Asked Questions

What is the simplest AVERAGEIF formula?
With categories in A2:A10 and values in B2:B10, use =AVERAGEIF(A2:A10,"North",B2:B10) to average the values associated with North. Replace the ranges and criterion with the cells in your worksheet.

Can AVERAGEIF use more than one condition?
No. AVERAGEIF applies one condition. Use AVERAGEIFS for two or more conditions, such as a region and a sales channel.

Does AVERAGEIF count zero values?
Yes. Numeric zeros in the average range are included in the average when their corresponding cells meet the criterion. Blank cells and text entries in that range are not averaged as numbers.

Why does AVERAGEIF return #DIV/0!?
It usually means Excel found no qualifying numeric values to average. Check that the criterion matches the data and that the corresponding average-range cells contain numbers rather than blanks or text.

Is AVERAGEIF case-sensitive?
No. Text criteria are not case-sensitive, so North and NORTH match alike. Wildcards can broaden a match, so use them only when matching partial text is intended.

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.