Need to calculate the average of values in Google Sheets—but only when certain conditions are met? That’s where AVERAGEIF and AVERAGEIFS come in. These functions allow you to easily calculate conditional averages, making your analysis much more precise. Whether you’re tracking sales, student grades, or project costs, these formulas help you dig deeper into your data without writing complex code.
If you’re new to Google Sheets or just looking to level up your spreadsheet skills, this guide is designed to be practical, beginner-friendly, and packed with real-life examples.
What Is AVERAGEIF?
AVERAGEIF is used to calculate the average of a range of numbers, but only if they meet a single condition.
=AVERAGEIF(range, criterion, [average_range])- range: The cells to check for the condition.
- criterion: The condition to apply (e.g., “>=70”, “Apples”).
- average_range (optional): The actual cells to average if different from the range being tested.
What Is AVERAGEIFS?
AVERAGEIFS lets you average values based on multiple conditions. All conditions must be true for a cell to be included in the average.
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)- average_range: The cells to average.
- criteria_range: The cells to apply conditions to.
- criterion: The condition to apply (e.g., “East”, “>100”).
Real-Life Example: Analyzing Student Grades
Imagine you’re a teacher and want to calculate the average score of students who passed (scored 60 or above) and compare it by subject.
| Student | Subject | Score |
|---|---|---|
| Emily | Math | 85 |
| James | Science | 58 |
| Liam | Math | 72 |
| Sophia | Science | 66 |
Use AVERAGEIF: Average Scores Above 60
=AVERAGEIF(C2:C5, ">=60")Result: 74.33 (average of 85, 72, and 66)
Use AVERAGEIFS: Average Math Scores ≥ 60
=AVERAGEIFS(C2:C5, B2:B5, "Math", C2:C5, ">=60")Result: 78.5 (average of 85 and 72)
Top Use Cases for AVERAGEIF and AVERAGEIFS
- Calculate the average sales in a region or by product type.
- Find the average attendance of events that had over 100 participants.
- Compare average test scores across different subjects or groups.
- Track the average delivery time for on-time shipments only.
Best Practices
- Use quotes for text and logical conditions (e.g., “
East“, “>=60“). - Always ensure that the
criteria_rangeandaverage_rangeare the same size. - Use cell references in criteria when possible (e.g.,
=AVERAGEIF(B2:B100, E1, C2:C100)). - AVERAGEIF ignores empty cells and cells with text when averaging.
Quick Cheat Sheet
| Task | Formula |
|---|---|
| Average all scores above 70 | =AVERAGEIF(C2:C100, “>70”) |
| Average scores in “Science” | =AVERAGEIF(B2:B100, “Science”, C2:C100) |
| Average sales > 100 in “East” region | =AVERAGEIFS(C2:C100, A2:A100, “East”, C2:C100, “>100”) |
| Average age of participants under 30 from “USA” | =AVERAGEIFS(C2:C100, B2:B100, “USA”, C2:C100, “<30”) |
AVERAGEIF and AVERAGEIFS in Google Sheets let you move beyond basic math and start uncovering real insights from your data. These functions are easy to learn and incredibly helpful, whether you’re managing business metrics, classroom data, or personal budgets.
Practice a few examples in your own spreadsheet to get comfortable, and soon you’ll be building smart summaries in no time!