Calculating the overall mean of a column using =AVERAGE() is simple, but practical reporting frequently requires conditional filtering. Whether you need to find the average deal size for a specific sales region, calculate average employee performance scores while ignoring zeros, or evaluate department expenses over a set time window, standard averages skew your numbers.
Google Sheets provides two specialized functions to handle this: AVERAGEIF (for single-condition averages) and AVERAGEIFS (for multi-criteria averages). In this guide, you will learn the exact formula syntax, how to avoid common zero-skew errors, and how to filter by text, dates, and numbers.
1. The AVERAGEIF Function: Single Criteria Mean
Use AVERAGEIF when your calculation depends on one specific rule (for example, finding the average order value only where the status is "Completed").
Formula Syntax
=AVERAGEIF(criteria_range, criterion, [average_range])criteria_range: The column evaluated against your condition (e.g.,A2:A50).criterion: The test value, text, or comparison rule (e.g.,"Completed"or">50").average_range: The column containing numeric values to average (e.g.,C2:C50). If omitted, Sheets averages thecriteria_rangedirectly.
Practical Example: Average by Category
If Column A contains department names and Column C contains employee salaries, find the average salary for the "Engineering" team using:
=AVERAGEIF(A2:A50, "Engineering", C2:C50)You can also reference a cell containing the department name instead of typing the text directly into the formula: =AVERAGEIF(A2:A50, E1, C2:C50).
2. How to Average Numbers Excluding Zeros
One of the most common issues in data analysis is zero values skewing an average downward (such as unanswered survey ratings or days with zero calls). To compute the average of only non-zero values:
=AVERAGEIF(B2:B100, "<>0")The "<>0" criterion instructs Google Sheets to ignore every cell containing a zero while calculating the mean of the remaining numbers.
3. The AVERAGEIFS Function: Multiple Criteria Averages
When you need to satisfy two or more conditions simultaneously, switch to AVERAGEIFS.
Important Syntax Notice: Just likeSUMIFS, the average_range comes FIRST inAVERAGEIFS. In single-criteriaAVERAGEIF, it comes last. Reversing this order is the most frequent cause of formula errors.
Formula Syntax
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])Practical Example: Multi-Condition Average
Suppose you want to calculate the average sale price in Column D where:
- The Region in Column A is "West"
- The Units Sold in Column C is greater than 10
=AVERAGEIFS(D2:D100, A2:A100, "West", C2:C100, ">10")4. Averaging Across Date Windows
To calculate the average daily transactions in Column C for the first quarter of 2026 (where transaction dates reside in Column A):
=AVERAGEIFS(C2:C100, A2:A100, ">=2026-01-01", A2:A100, "<=2026-03-31")If your dates are dynamic and stored in cells F1 and F2, use string concatenation:
=AVERAGEIFS(C2:C100, A2:A100, ">="&F1, A2:A100, "<="&F2)5. AVERAGEIF vs. AVERAGEIFS Reference Table
| Feature | AVERAGEIF | AVERAGEIFS |
|---|---|---|
| Total Conditions | Exactly 1 | Up to 127 conditions |
| Position of Average Range | Last argument: (range, test, [avg_range]) |
First argument: (avg_range, range1, test1, ...) |
| Default When Range Omitted | Averages the criteria range directly | N/A (Average range is mandatory) |
| Best Used For | Quick category benchmarks & non-zero filters | Multi-layered business KPI dashboards |
Frequently Asked Questions
Why does my formula return a #DIV/0! error?
A #DIV/0! error occurs when zero cells match your criteria (division by zero). To display a clean message or fallback value instead of the error, wrap your formula in IFERROR: =IFERROR(AVERAGEIF(A2:A, "Sales", B2:B), 0).
Do blank cells count toward the average?
No. Google Sheets automatically skips completely blank cells when calculating the average. However, cells containing literal 0 values are included unless explicitly excluded using "<>0".
Recommended Next Steps
Conditional averages reveal meaningful performance trends across complex spreadsheets. Continue mastering business formulas with these related guides:
- Conditional Totals: Need to calculate the total sum instead of the mean? Read our complete tutorial on how to use SUMIF and SUMIFS in Google Sheets.
- Track Growth Rates: Compare your calculated averages month-over-month using our guide on how to calculate percentage change in Google Sheets.
- Extract Clean Unique Groups: Get distinct category lists before running average formulas with our guide on how to count unique values in Google Sheets.
- Automate Lookups: Retrieve specific record benchmarks alongside your summary averages using our tutorial on how to VLOOKUP with multiple criteria in Google Sheets.
Comments
Post a Comment