AVERAGEIF in Google Sheets: Syntax and Examples

To average values in Google Sheets only when they meet a condition, use AVERAGEIF. For example, with regions in A2:A6 and sales in B2:B6, =AVERAGEIF(A2:A6,"East",B2:B6) averages the sales for rows marked East; if those sales are 120, 150, and 180, the result is 150.

What AVERAGEIF does

AVERAGEIF calculates the arithmetic mean of values that satisfy one condition. It is useful when a list contains different categories and you want the average for just one of them: sales for a particular region, scores above a threshold, or hours logged by one employee. The condition is applied row by row, and only the matching values contribute to the result.

The function separates the cells it checks from the cells it averages. Those ranges can be the same, but they do not have to be. For instance, you might check department names in one column and average the corresponding wages in another. This is the “if” in the function: include a value in the average if its paired condition is true.

If you want the average of every numeric value in a range without filtering, use AVERAGE instead. With test scores 80, 90, and 100 in A2:A4, the formula =AVERAGE(A2:A4) returns 90. That formula has no condition; use AVERAGEIF when only some of the cells should count.

Syntax and arguments

In the syntax below, range is the group of cells checked against the condition, criterion describes the condition, and the optional average_range contains the numbers to average. The ranges should cover corresponding rows.

=AVERAGEIF(range, criterion, [average_range])

The square brackets in the displayed syntax indicate an optional argument; do not type them into a formula. If you leave out average_range, Sheets checks and averages the same cells. If you supply it, each row in the average range is associated with the cell in the same position in the criteria range. Keeping both ranges the same size makes the relationship clear and helps prevent unexpected results.

A criterion can be a text value, a number, a comparison such as “greater than 100,” or a reference to a cell containing the criterion. Text conditions need quotation marks when typed directly into a formula. A cell reference used as the criterion is not put in quotation marks.

A basic category example

Suppose A2:A6 contains East, West, East, West, East, and B2:B6 contains 120, 90, 150, 110, 180, respectively. With regions in A2:A6 and sales in B2:B6, this formula returns 150:

=AVERAGEIF(A2:A6,"East",B2:B6)

Only the three sales figures on East rows are included: (120 + 150 + 180) ÷ 3 = 150. The West values do not affect the result. If the category text is stored in D2 instead, the formula can refer to that cell, so changing D2 from East to West changes which rows are averaged:

With regions in A2:A6, sales in B2:B6, and the chosen region in D2, =AVERAGEIF(A2:A6,D2,B2:B6) returns 100 when D2 contains West, because (90 + 110) ÷ 2 = 100.

Using a cell reference is handy for reports and dashboards: the formula stays in place while a user changes the selection. It also avoids accidentally including a space or misspelling the criterion inside the formula.

Using numbers, comparisons, dates, and wildcards

For a numerical condition, put a comparison operator and value in quotation marks. For example, with scores in A2:A6 containing 80, 120, 100, 160, and 140, the formula below averages only values greater than 100. The result is 140: (120 + 160 + 140) ÷ 3.

With scores in A2:A6, =AVERAGEIF(A2:A6,">100") returns 140.

To compare against a value stored in a cell, join the operator to the cell reference with the ampersand. With scores in A2:A6 and a numeric threshold in D2, =AVERAGEIF(A2:A6,">"&D2) averages scores greater than D2. If D2 is 100 and the scores are the five values above, it returns 140. The ampersand combines the comparison sign with the value from D2 to form the criterion.

Dates can also be criteria, provided the cells contain actual date values rather than text that merely looks like a date. With dates in A2:A20, amounts in B2:B20, and date values in the date column, this formula averages amounts dated on or after January 1, 2026:

With transaction dates in A2:A20 and amounts in B2:B20, =AVERAGEIF(A2:A20,">="&DATE(2026,1,1),B2:B20) returns the mean of the amounts on qualifying rows. The actual result depends on the data in those cells. Using DATE avoids relying on how a particular locale interprets a typed date.

Text criteria can use wildcards. An asterisk matches any number of characters, while a question mark matches one character. For example, “North*” matches names beginning with North, such as North and Northeast. With labels in A2:A20 and amounts in B2:B20, =AVERAGEIF(A2:A20,"North*",B2:B20) averages amounts paired with matching labels. Wildcards are most useful when categories follow a predictable naming pattern.

Average with more than one condition

AVERAGEIF accepts one condition. For two or more conditions that must all be true, use AVERAGEIFS. Its argument order differs: put the range to average first, followed by each criteria range and its condition. That reversed order is a common source of mistakes when switching between the two functions.

Suppose A2:A6 contains regions, B2:B6 contains months, and C2:C6 contains sales: East/Jan/100, East/Feb/200, West/Jan/300, East/Jan/150, and West/Feb/250. With those columns in the stated ranges, the formula below returns 125, the average of the two East sales from January: (100 + 150) ÷ 2.

=AVERAGEIFS(C2:C6,A2:A6,"East",B2:B6,"Jan")

Each additional pair adds another requirement. In this example, a row must match East in column A and Jan in column B to contribute its value from column C. A row matching only one of the two conditions is excluded. Keep the average range and all criteria ranges aligned to the same rows so each set of conditions is evaluated against the intended record.

Common problems and how to fix them

If the formula returns #DIV/0!, check whether any cells match the condition and whether the matching average-range cells contain numeric values. An average cannot be calculated when there are no numeric values to include. Check for misspelled category names, extra spaces, criteria that use the wrong comparison sign, and text-formatted numbers. If you need a friendlier message when no rows match, you can handle the error with IFERROR.

With categories in A2:A20, values in B2:B20, and “East” as the condition, =IFERROR(AVERAGEIF(A2:A20,"East",B2:B20),"No matching values") displays the message rather than an error when the average cannot be calculated. This can make a summary more readable, but it can also conceal a mistake in the criteria, so use it where a no-match result is a reasonable possibility.

Also check that the criteria range and average range correspond row for row. If one starts on row 2 and the other on row 3, values can be paired with the wrong categories. In a sheet where rows are inserted or data is copied, verify the references in the formula bar. For recurring work, use consistent column headers and avoid including a header cell in one range but not the other.

When a formula is rejected as a parse error, inspect its commas, quotation marks, and parentheses. Google Sheets’ formula separators can depend on the spreadsheet’s locale; some locales use semicolons instead of commas. The spreadsheet locale can be reviewed under File > Settings. In a shared sheet, consider whether changing its locale would affect date or number display for other users before doing so.

AVERAGEIF compared with related functions

Choose the function according to the question being asked:

AVERAGE averages numeric cells in a range without a condition.
AVERAGEIF averages values that meet one condition.
AVERAGEIFS averages values that meet multiple conditions at the same time.
COUNTIF counts cells meeting a condition; it does not calculate an average.

For example, if the question is “What is the average sales amount for East?”, use AVERAGEIF. If it is “What is the average East sales amount in January?”, use AVERAGEIFS. If it is “How many East rows are there?”, use COUNTIF. Matching the function to the question makes the formula easier to read and reduces the chance of averaging the wrong records.

The core AVERAGEIF and AVERAGEIFS formulas are also available in Excel, and their basic argument patterns are the same. The interface differs: these instructions refer to Google Sheets in a web browser, while Excel uses its own ribbon and desktop interface. If a formula behaves differently between spreadsheets, check the locale and the exact cell data as well as the formula itself.

Frequently Asked Questions

What is the formula for average if in Google Sheets?
Use =AVERAGEIF(range,criterion,average_range) when you want to average values that meet one condition. The final argument is optional if you are averaging the same range you are checking.

How do I average cells greater than a number?
Put the comparison in quotation marks, such as ">100", as the criterion. If the threshold is stored in a cell, join the operator to that cell reference with &.

Can AVERAGEIF use two conditions?
No. AVERAGEIF takes one condition. Use AVERAGEIFS when all of several conditions must be satisfied, and remember that AVERAGEIFS starts with the range to average.

Does AVERAGEIF ignore blank cells?
Blank cells do not contribute numeric values to an average. If no matching numeric values remain, Sheets cannot calculate a mean and may return #DIV/0!.

Why does AVERAGEIF return the wrong result?
Check that the criteria text matches the data, both ranges line up row by row, and the values being averaged are numeric. Also review comparison operators, spaces, date formatting, and the spreadsheet’s locale if the formula has a parse error.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.