AVERAGEIF and AVERAGEIFS in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the AVERAGEIF function do in Google Sheets?

AVERAGEIF calculates the average (mean) of numbers in a range that meet a single condition.

beginner

How is AVERAGEIFS different from AVERAGEIF?

AVERAGEIFS calculates the average of numbers in a range that meet multiple conditions at the same time.

beginner

Write the syntax of AVERAGEIF.

AVERAGEIF(range, criterion, [average_range])

- range: cells to check the condition
- criterion: condition to meet
- average_range: cells to average (optional, if different from range)

beginner

Write the syntax of AVERAGEIFS.

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

- average_range: cells to average
- criteria_range1: first range to check
- criterion1: first condition
- Additional pairs for more conditions

beginner

Example: How to find the average score of students who scored more than 70 using AVERAGEIF?

Use: =AVERAGEIF(B2:B10, ">70")
This averages scores in B2:B10 where the score is greater than 70.

What does the AVERAGEIFS function allow you to do that AVERAGEIF does not?

A Sum numbers based on one condition

B Average numbers without any condition

C Count how many cells meet a condition

D Average numbers based on multiple conditions

AVERAGEIFS lets you average numbers that meet several conditions at once, unlike AVERAGEIF which only uses one condition.

Which argument is optional in the AVERAGEIF function?

A criterion

B average_range

C range

D none

The average_range is optional; if omitted, the function averages the cells in the range argument.

If you want to average values in C2:C10 where A2:A10 equals "Yes" and B2:B10 is greater than 50, which function should you use?

A AVERAGEIF

B SUMIF

C AVERAGEIFS

D COUNTIF

AVERAGEIFS supports multiple conditions, so it fits this need.

What symbol is used in criteria to mean 'greater than' in AVERAGEIF?

A >

B <

C =

D !

The > symbol means 'greater than' in criteria.

True or False: AVERAGEIF can average cells based on text conditions like "apple".

A True

B False

C Only with numbers

D Only with dates

AVERAGEIF can use text conditions to average cells where the condition matches text.

Explain how you would use AVERAGEIF to find the average sales greater than $1000 in a list.

Think about which cells hold sales and what condition to write.

Describe a situation where AVERAGEIFS is better than AVERAGEIF and how you would write the formula.

Use more than one condition to filter data.

Practice

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