Bird
Raised Fist0
Google Sheetsspreadsheet~10 mins

AVERAGEIF and AVERAGEIFS in Google Sheets - Cell-by-Cell Formula Trace

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

This table shows names of students in column A and their test scores in column B.

CellValue
A1Name
B1Score
A2Alice
B285
A3Bob
B390
A4Alice
B478
A5Charlie
B592
A6Bob
B688
A7Alice
B795
Formula Trace
=AVERAGEIF(A2:A7, "Alice", B2:B7)
Step 1: Check each cell in A2:A7 if it equals "Alice"
Step 2: Select scores from B2:B7 where corresponding A cells are TRUE
Step 3: Calculate average of [85, 78, 95]
Cell Reference Map
    A       B
1 |Name   |Score
2 |Alice  | 85  
3 |Bob    | 90  
4 |Alice  | 78  
5 |Charlie| 92  
6 |Bob    | 88  
7 |Alice  | 95  

Formula references:
- A2:A7 for criteria range
- B2:B7 for average range
The formula looks at names in A2:A7 to find 'Alice' and averages the matching scores in B2:B7.
Result
    A       B       C
1 |Name   |Score  | Result
2 |Alice  | 85    | 86
3 |Bob    | 90    |
4 |Alice  | 78    |
5 |Charlie| 92    |
6 |Bob    | 88    |
7 |Alice  | 95    |
The result 86 is the average score of all rows where the name is Alice.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does the formula =AVERAGEIF(A2:A7, "Alice", B2:B7) do?
AIt averages scores in B2:B7 only for rows where A2:A7 is 'Alice'.
BIt averages all scores in B2:B7 regardless of names.
CIt counts how many times 'Alice' appears in A2:A7.
DIt sums all scores in B2:B7 for 'Bob'.
Key Result
AVERAGEIF averages values in one range based on a single condition in another range.

Practice

(1/5)
1. What does the AVERAGEIF function do in Google Sheets?
easy
A. Finds the maximum number in a range.
B. Calculates the sum of all numbers in a range.
C. Counts how many cells contain numbers.
D. Calculates the average of numbers that meet one specific condition.

Solution

  1. Step 1: Understand the purpose of AVERAGEIF

    The AVERAGEIF function calculates the average of numbers that satisfy a single condition.

  2. Step 2: Compare with other options

    Options B, C, and D describe different functions: SUM, COUNT, and MAX, which do not average with conditions.

  3. Final Answer:

    Calculates the average of numbers that meet one specific condition. -> Option D

  4. Quick Check:

    AVERAGEIF = average with one condition [OK]
Hint: AVERAGEIF averages numbers with one condition only [OK]
Common Mistakes:
  • Confusing AVERAGEIF with SUM or COUNT functions
  • Thinking it works with multiple conditions
  • Assuming it counts cells instead of averaging
2. Which of the following is the correct syntax for AVERAGEIFS in Google Sheets?
easy
A. AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
B. AVERAGEIFS(criteria_range, average_range, criterion)
C. AVERAGEIFS(range, condition)
D. AVERAGEIFS(average_range, criterion1, criteria_range1)

Solution

  1. Step 1: Recall AVERAGEIFS syntax

    The correct syntax is AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) where average_range is first.

  2. Step 2: Check each option

    AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) matches the correct syntax. Options A, B, and C have wrong argument order or missing parts.

  3. Final Answer:

    AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) -> Option A

  4. Quick Check:

    AVERAGEIFS syntax = average_range first [OK]
Hint: Remember: average_range is always first in AVERAGEIFS [OK]
Common Mistakes:
  • Swapping average_range and criteria_range
  • Using only one criteria without brackets
  • Missing criteria pairs for multiple conditions
3. Given the data:
A1:A5 = {10, 20, 30, 40, 50}
B1:B5 = {"Yes", "No", "Yes", "No", "Yes"}
What is the result of =AVERAGEIF(B1:B5, "Yes", A1:A5)?
medium
A. 30
B. 20
C. 40
D. 50

Solution

  1. Step 1: Identify values matching the condition

    Condition is "Yes" in B1:B5. Matching rows are 1, 3, and 5 with values 10, 30, and 50 in A1:A5.

  2. Step 2: Calculate average of matching values

    Average = (10 + 30 + 50) / 3 = 90 / 3 = 30.

  3. Final Answer:

    30 -> Option A

  4. Quick Check:

    Average of 10, 30, 50 = 30 [OK]
Hint: Average only values where condition matches [OK]
Common Mistakes:
  • Averaging all values ignoring condition
  • Using wrong range for criteria
  • Confusing AVERAGEIF with COUNTIF
4. You wrote this formula:
=AVERAGEIFS(A1:A5, B1:B5, ">20", C1:C5, "Yes")
But it returns an error. What is the likely problem?
medium
A. The criteria ">20" should be in quotes, but it is missing.
B. The ranges A1:A5, B1:B5, and C1:C5 must be the same size.
C. AVERAGEIFS does not support multiple criteria.
D. AVERAGEIFS requires only one criteria range.

Solution

  1. Step 1: Check criteria syntax

    The criteria ">20" is correctly in quotes, so that's not the problem.

  2. Step 2: Verify range sizes

    AVERAGEIFS requires all ranges (average_range and criteria_ranges) to be the same size. If they differ, it causes an error.

  3. Final Answer:

    The ranges A1:A5, B1:B5, and C1:C5 must be the same size. -> Option B

  4. Quick Check:

    Range sizes must match in AVERAGEIFS [OK]
Hint: Check all ranges have same number of rows [OK]
Common Mistakes:
  • Using different sized ranges
  • Forgetting quotes around criteria
  • Thinking AVERAGEIFS supports only one condition
5. You have sales data:
Sales (A2:A7): {100, 200, 150, 300, 250, 400}
Region (B2:B7): {"East", "West", "East", "West", "East", "West"}
Status (C2:C7): {"Complete", "Complete", "Pending", "Complete", "Pending", "Complete"}
Which formula calculates the average sales for the East region with status Complete?
hard
A. =AVERAGEIF(A2:A7, "East", B2:B7) * AVERAGEIF(A2:A7, "Complete", C2:C7)
B. =AVERAGEIF(B2:B7, "East", A2:A7) + AVERAGEIF(C2:C7, "Complete", A2:A7)
C. =AVERAGEIFS(A2:A7, B2:B7, "East", C2:C7, "Complete")
D. =AVERAGEIFS(B2:B7, A2:A7, "East", C2:C7, "Complete")

Solution

  1. Step 1: Understand the requirement

    We want average of sales (A2:A7) where Region is "East" and Status is "Complete".

  2. Step 2: Check each formula

    =AVERAGEIFS(A2:A7, B2:B7, "East", C2:C7, "Complete") correctly uses AVERAGEIFS with sales as average_range and two criteria ranges with conditions. Options B, C, and D misuse ranges or functions.

  3. Final Answer:

    =AVERAGEIFS(A2:A7, B2:B7, "East", C2:C7, "Complete") -> Option C

  4. Quick Check:

    Use AVERAGEIFS for multiple conditions [OK]
Hint: Use AVERAGEIFS with average_range first, then criteria pairs [OK]
Common Mistakes:
  • Adding averages instead of using AVERAGEIFS
  • Mixing up average_range and criteria_range
  • Using AVERAGEIF for multiple conditions
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard