# Google Sheets AVERAGEIFS Function: Syntax, Examples & Tips

- URL: https://betteranalyst.com/google-sheets/averageifs-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 that meet multiple conditions.

```
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
```

Generate FormulaExplain Formula

One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward.

Describe the AVERAGEIFS you need in plain EnglishGenerate Formula

## Parameters

| Parameter | Required | Description |
| - | - | - |
| **average\_range** | Yes | The range of values to average. |
| **criteria\_range1** | Yes | The first range to test. |
| **criterion1** | Yes | The condition applied to **criteria\_range1**. |
| **criteria\_range2, criterion2, ...** | No | Additional range and condition pairs. A row is included only if it meets every condition. |

## What Is AVERAGEIFS?

AVERAGEIFS calculates the mean of values that satisfy **all** of several conditions. It extends [AVERAGEIF](https://betteranalyst.com/google-sheets/averageif-function) the same way SUMIFS extends [SUMIF](https://betteranalyst.com/google-sheets/sumif-function). Typical questions: "What was the average order value in the West region during Q3?" or "What is the average score for Grade 10 students who passed?"

## How AVERAGEIFS Works

The first argument is the range you want to average. After it come one or more pairs of a criteria range and a criterion. For each row, AVERAGEIFS checks every condition. Only rows that pass all of them (AND logic) are included. Text and blank cells in **average\_range** are ignored.

Every criteria range must be the same size and shape as **average\_range**.

## Basic Examples

### Two text conditions

```
=AVERAGEIFS(D2:D500, A2:A500, "West", B2:B500, "Enterprise")
```

Averages column D for rows where the region is "West" and the segment is "Enterprise".

### A numeric range

```
=AVERAGEIFS(C2:C500, C2:C500, ">=100", C2:C500, "<=500")
```

Averages values between 100 and 500 inclusive. The same range can appear as both the averaged range and a criteria range.

### A date window

```
=AVERAGEIFS(C2:C500, A2:A500, ">="&DATE(2026,7,1), A2:A500, "<"&DATE(2026,10,1))
```

Averages column C for Q3 2026.

### Criteria from cells

```
=AVERAGEIFS($D$2:$D$500, $A$2:$A$500, G2, $B$2:$B$500, H$1)
```

Uses a region in column G and a segment in row 1 to fill a summary grid of averages.

## Operators and Wildcards

AVERAGEIFS uses the same criterion syntax as AVERAGEIF:

- Comparison operators in quotes: `">10"`, `"<>Cancelled"`
- Joined to a cell value with `&`: `">"&F1`
- Wildcards for text: `"*widget*"` (contains), `"A?"` (two characters starting with A)
- `"<>"` on its own matches non-empty cells, and `""` matches empty ones

## OR Logic

AVERAGEIFS cannot express "region is West **or** East" within one criterion. Instead, calculate a combined average from sums and counts:

```
=(SUMIFS(D:D, A:A, "West") + SUMIFS(D:D, A:A, "East")) / (COUNTIFS(A:A, "West", D:D, "<>") + COUNTIFS(A:A, "East", D:D, "<>"))
```

Or use AVERAGE with FILTER for more flexible conditions.

## AVERAGEIF vs. AVERAGEIFS

| Feature | AVERAGEIF | AVERAGEIFS |
| - | - | - |
| Conditions | One | One or more |
| Averaged range position | Last, optional | First, required |
| Logic between conditions | n/a | AND |

## Common Use Cases

- **Sales analysis**: average deal value by region, rep, and quarter
- **HR**: average salary by department and level
- **Marketing**: average CPC by channel and month
- **Operations**: average handling time by team and priority
- **Education**: average marks by class and term

## Common Errors

- **#DIV/0!**: No row met every condition. Use `=IFERROR(AVERAGEIFS(...), "")` to display a blank.
- **#VALUE! from size mismatch**: All ranges must have the same dimensions. `D2:D500` with `A2:A499` fails.
- **Argument order**: Putting the averaged range last (AVERAGEIF style) returns wrong results or errors. It always comes first in AVERAGEIFS.
- **Unquoted operators**: Write `">=100"`, not `>=100`.

## Tips and Best Practices

- Lock ranges with `$` before copying a summary formula across a grid.
- Use whole columns (`A:A`) only when the sheet has no stray values below the data.
- Check how many rows contributed with COUNTIFS using the same conditions.
- Text criteria are not case-sensitive.

## Related Functions

- [AVERAGEIF](https://betteranalyst.com/google-sheets/averageif-function): single-condition average
- [AVERAGE](https://betteranalyst.com/google-sheets/average-function): unconditional mean
- [SUMIF](https://betteranalyst.com/google-sheets/sumif-function): conditional sum
- [IF](https://betteranalyst.com/google-sheets/if-function): custom logic for edge cases

## Example use case

An e-commerce analyst wants the average order value for returning customers in the UK during October. =AVERAGEIFS(E2:E5000, B2:B5000, "UK", C2:C5000, "Returning", A2:A5000, ">="\&DATE(2026,10,1)) returns it in one formula without building a pivot table.

## 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 Make A Graph In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-graph-in-google-sheets)
- [How To Make A Histogram In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-histogram-in-google-sheets)
- [How To Make A Line Graph In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-line-graph-in-google-sheets)
- [How To Make A Pie Chart In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-pie-chart-in-google-sheets)
- [How To Make A Table In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-table-in-google-sheets)
- [How To Merge Cells In Google Sheets](https://betteranalyst.com/google-sheets/how-to-merge-cells-in-google-sheets)
- [How To Multiply In Google Sheets](https://betteranalyst.com/google-sheets/how-to-multiply-in-google-sheets)
- [How To Password Protect An Google Sheets File](https://betteranalyst.com/google-sheets/how-to-password-protect-an-google-sheets-file)

## 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.