Conditional formula page
AVERAGEIF Formula
Use AVERAGEIF when one criteria range controls which numbers should be averaged.
Average values by one condition with AVERAGEIF.
With the sample data, this averages Amount values for East rows.
Copy formulas
=AVERAGEIF(B2:B100, "East", D2:D100)=AVERAGEIF(B2:B100, "East", D2:D100)Excel and Google Sheets use the same formula syntax for this pattern.
Example data
| Date | Region | Product | Amount | Rep |
|---|---|---|---|---|
| 2026-01-04 | East | Widget | 420 | Maya |
| 2026-01-12 | West | Widget | 310 | Noah |
| 2026-02-03 | East | Gadget | 275 | Maya |
| 2026-02-15 | East | Widget | 640 | Iris |
Formula explanation
- B2:B100 chooses matching rows.
- D2:D100 supplies the values to average.
- Blank and nonnumeric values in the average range are ignored by spreadsheet rules.
What this formula does
AVERAGEIF Formula is built around AVERAGEIF. The example formula on this page is =AVERAGEIF(B2:B100, "East", D2:D100), and it is written against sample columns such as Date, Region, Product, Amount, Rep.
Excel and Google Sheets use the same copy pattern here, so the main work is adjusting ranges, criteria, and output cells to match your sheet.
Criteria formulas test each row before returning a total, count, or average. Every criteria range should cover the same row numbers as the result range so row-level logic stays aligned.
Use this pattern for this task: Average values by one condition with AVERAGEIF. Date and text criteria should be real spreadsheet values, not display-only text that only looks correct on screen.
| Syntax piece | Role in the formula |
|---|---|
| B2:B100 | B2:B100 chooses matching rows. |
| D2:D100 | D2:D100 supplies the values to average. |
| Part 3 | Blank and nonnumeric values in the average range are ignored by spreadsheet rules. |
Practical use cases
=AVERAGEIF(B2:B100, "East", D2:D100)Excel: Use this in a sales, task, or reporting table where each row is tested before the final number is returned. Returns: With the sample data, this averages Amount values for East rows.
=AVERAGEIF(B2:B100, "East", D2:D100)Google Sheets: Use this for dashboards where changing a criterion cell should refresh the result without rebuilding a pivot table. Returns: With the sample data, this averages Amount values for East rows.
Common errors
- Criteria ranges must cover the same rows as the sum, count, or average range.
- Text criteria need quotes when typed manually.
- Date criteria should compare real date values, not month names typed as text.
Common errors and troubleshooting
| Issue | Likely cause | Fix |
|---|---|---|
| Copied formula gives an unexpected result | Criteria ranges must cover the same rows as the sum, count, or average range. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Common setup problem | Text criteria need quotes when typed manually. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Common setup problem | Date criteria should compare real date values, not month names typed as text. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Formula returns 0 | The criteria text, date value, or numeric comparison does not match the source cells exactly. | Test the criterion against one row, then confirm all criteria ranges start and end on the same rows. |
| #VALUE! from criteria ranges | One criteria range is a different shape from the sum, count, or average range. | Use matching row counts such as A2:A100 with D2:D100 instead of mixing whole-column and partial-column references. |
When not to use this formula
- Do not use criteria formulas to return matching rows; use FILTER or QUERY for row output.
- Do not use SUMIF or COUNTIF when you need several independent conditions; use the IFS version instead.
Alternatives
| Alternative | When to use it |
|---|---|
| SUMIFS Formula Builder | Use when the result should be a total amount. |
| COUNTIFS Formula Builder | Use when the result should be a row count. |
| SUMIFS vs COUNTIFS Guide | Use this guide when you are choosing between totals and counts. |
Compare with related formulas
| Formula or tool | How it differs | Use it instead when |
|---|---|---|
| SUMIFS Formula Builder | SUMIFS Formula Builder solves a nearby workflow, while AVERAGEIF Formula is focused on average values by one condition with averageif. | Use when the result should be a total amount. |
| COUNTIFS Formula Builder | COUNTIFS Formula Builder solves a nearby workflow, while AVERAGEIF Formula is focused on average values by one condition with averageif. | Use when the result should be a row count. |
| SUMIFS vs COUNTIFS Guide | SUMIFS vs COUNTIFS Guide solves a nearby workflow, while AVERAGEIF Formula is focused on average values by one condition with averageif. | Use this guide when you are choosing between totals and counts. |
Test cases
| Test | Platform | Formula | Returns |
|---|---|---|---|
| Excel copy output | Excel | =AVERAGEIF(B2:B100, "East", D2:D100) | With the sample data, this averages Amount values for East rows. |
| Google Sheets copy output | Google Sheets | =AVERAGEIF(B2:B100, "East", D2:D100) | With the sample data, this averages Amount values for East rows. |
Related formulas
FAQ
What does the AVERAGEIF Formula do?
It supports this task: Average values by one condition with AVERAGEIF. The example uses B2:B100, but you should replace the ranges and criteria with the cells in your own workbook.
Does the AVERAGEIF Formula work in Excel and Google Sheets?
Yes. The Excel and Google Sheets versions on this page use the same syntax for this pattern.
What should I change before copying the formula?
Change the source ranges, criteria cells, and output range references so they match your sheet. Keep related ranges on the same rows unless the formula notes say otherwise.
Why does this criteria formula return zero?
The criteria may not match the source cells exactly, or the criteria ranges may not align with the result range. Test one row and then confirm the full ranges.
Can I add another condition?
Use the IFS version of the formula when you need multiple independent criteria, such as region plus product plus date.
How should I handle dates in criteria formulas?
Use real date cells or date functions such as DATE and EOMONTH. Month names typed as text are easier to break.