# Google Sheets AVERAGEIF Function: Syntax, Examples & Tips - URL: https://betteranalyst.com/google-sheets/averageif-function - Author: Better Analyst Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation. [Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst) Returns the average of values in a range that meet a single condition. ``` =AVERAGEIF(criteria_range, criterion, [average_range]) ``` Generate FormulaExplain Formula One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward. Describe the AVERAGEIF you need in plain EnglishGenerate Formula ## Parameters | Parameter | Required | Description | | - | - | - | | **criteria\_range** | Yes | The range to test against the criterion. | | **criterion** | Yes | The condition to apply, such as `"West"`, `">100"`, or a cell reference. | | **average\_range** | No | The cells to average. If omitted, the cells in **criteria\_range** are averaged. | ## What Is AVERAGEIF? AVERAGEIF returns the mean of the values that meet one condition. It works like [SUMIF](https://betteranalyst.com/google-sheets/sumif-function), except that it divides the matching total by the number of matches. Use it when you want an answer such as "average order value for the West region" or "average score above 50". ## How AVERAGEIF Works AVERAGEIF checks every cell in **criteria\_range** against the **criterion**. For each match, the cell in the same position in **average\_range** is included in the average. If you leave out **average\_range**, AVERAGEIF averages the matching cells in **criteria\_range** itself. Text and blank cells in the averaged range are ignored, just as with [AVERAGE](https://betteranalyst.com/google-sheets/average-function). ## Basic Examples ### Average by category ``` =AVERAGEIF(A2:A200, "West", C2:C200) ``` Averages column C for every row where column A is "West". ### Average values above a threshold ``` =AVERAGEIF(B2:B100, ">50") ``` Averages only the numbers in B2:B100 that are greater than 50. ### Exclude zeros ``` =AVERAGEIF(B2:B100, "<>0") ``` Averages every non-zero number, a common way to ignore placeholder zeros. ### Use a cell as the criterion ``` =AVERAGEIF(A2:A200, F1, C2:C200) ``` Lets you change the category in F1 and see the result update. ## Comparison Operators | Operator | Meaning | Example | | - | - | - | | `>` | Greater than | `">100"` | | `<` | Less than | `"<10"` | | `>=` | Greater than or equal to | `">=0"` | | `<=` | Less than or equal to | `"<=5"` | | `<>` | Not equal to | `"<>Closed"` | Combine an operator with a cell value using `&`: ``` =AVERAGEIF(B2:B100, ">="&E1) ``` ## Wildcards For text criteria, `*` matches any run of characters and `?` matches exactly one: ``` =AVERAGEIF(A2:A200, "*Pro*", C2:C200) ``` Averages column C for any product name that contains "Pro". To match a literal asterisk or question mark, put a tilde in front (`~*`). ## Dates as Criteria ``` =AVERAGEIF(A2:A365, ">="&DATE(2026,7,1), B2:B365) ``` Averages values for dates on or after 1 July 2026. Build date criteria with DATE rather than typing a date string, to avoid locale problems. ## Common Use Cases - **Sales**: average deal size per rep or region - **Support**: average resolution time for one priority level - **Education**: average score for a class or section - **Marketing**: average cost per click for one channel - **Data cleaning**: averages that skip zeros or a "test" category ## Common Errors - **#DIV/0!**: No cells matched the criterion, or the matching cells contain no numbers. Wrap with IFERROR to show a blank: `=IFERROR(AVERAGEIF(...), "")`. - **Operator outside quotes**: `>50` must be written as `">50"`. - **Mismatched ranges**: Keep **criteria\_range** and **average\_range** the same size and aligned on the same rows. - **Numbers stored as text**: These are skipped in the averaged range and can produce a misleading average. ## Tips and Best Practices - Text matching is not case-sensitive: `"west"` matches "West". - For two or more conditions, switch to [AVERAGEIFS](https://betteranalyst.com/google-sheets/averageifs-function), which puts the averaged range first. - To count how many rows the average is based on, pair it with COUNTIF using the same criterion. ## Related Functions - [AVERAGEIFS](https://betteranalyst.com/google-sheets/averageifs-function): averages with multiple conditions - [AVERAGE](https://betteranalyst.com/google-sheets/average-function): unconditional mean - [SUMIF](https://betteranalyst.com/google-sheets/sumif-function): sums with a condition - [IF](https://betteranalyst.com/google-sheets/if-function): build more complex logic ## Example use case A sales director wants to compare regions. With regions in column A and deal values in column C, =AVERAGEIF(A2:A500, "West", C2:C500) returns the average deal size in the West, and changing the criterion to a cell reference lets one formula serve every region. ## Need more help with Google Sheets formulas? Sign up for Better Analyst and get unlimited AI-powered formula generation, explanations, and advanced data analysis. [Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai) ## Google Sheets how-to guides - [How To Password Protect An Google Sheets File](https://betteranalyst.com/google-sheets/how-to-password-protect-an-google-sheets-file) - [How To Remove Blank Rows In Google Sheets](https://betteranalyst.com/google-sheets/how-to-remove-blank-rows-in-google-sheets) - [How To Remove Duplicates In Google Sheets](https://betteranalyst.com/google-sheets/how-to-remove-duplicates-in-google-sheets) - [How To Separate Names In Google Sheets](https://betteranalyst.com/google-sheets/how-to-separate-names-in-google-sheets) - [How To Sort In Google Sheets](https://betteranalyst.com/google-sheets/how-to-sort-in-google-sheets) - [How To Split Cells In Google Sheets](https://betteranalyst.com/google-sheets/how-to-split-cells-in-google-sheets) - [How To Strikethrough In Google Sheets](https://betteranalyst.com/google-sheets/how-to-strikethrough-in-google-sheets) - [How To Subtract In Google Sheets](https://betteranalyst.com/google-sheets/how-to-subtract-in-google-sheets) ## Explore all Google Sheets formulas Browse our complete library of Google Sheets formula guides Compare your options ## Decide with evidence, not a sales pitch. Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose. 1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.