AVERAGEIF and AVERAGEIFS in Google Sheets - Step-by-Step Guide

Introduction

AVERAGEIF and AVERAGEIFS help you find the average of numbers that meet certain conditions. This is useful when you want to calculate an average but only for specific data that fits your rules.

When you want to find the average sales only for a specific product.

When you need the average test score for students who scored above 70.

When calculating the average hours worked by employees in a certain department.

When you want the average price of items that are in stock.

When you want to average expenses only for a certain month.

Steps

Step 1: Click

- a blank cell where you want the average result

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

The formula starts to appear in the formula bar

💡 Start with =AVERAGEIF( for one condition or =AVERAGEIFS( for multiple conditions

Step 3: Enter

- the range of cells to check the condition (e.g., A2:A10)

The formula now knows which cells to look at for the condition

Step 4: Type

- the condition in quotes (e.g., ">70" or "Apples")

The formula understands which values to include based on this rule

Step 5: Enter

- the range of cells to average (only for AVERAGEIF, optional if same as range)

The formula knows which numbers to average

Step 6: Press

- Enter key

The cell shows the average of numbers that meet the condition

Step 7: For multiple conditions, repeat

- steps 3 to 4 inside AVERAGEIFS formula for each condition

The formula averages numbers that meet all conditions

Before vs After

Before

A list of sales amounts in column B and product names in column A with no averages calculated

After

A cell shows the average sales amount only for the product 'Apples' using =AVERAGEIF(A2:A10, "Apples", B2:B10)

Settings Reference

Range

📍 First argument in AVERAGEIF or AVERAGEIFS formula

Cells to check for the condition

Default: No default, must specify

Criteria

📍 Second argument in AVERAGEIF or subsequent arguments in AVERAGEIFS

Condition to decide which cells to include

Default: No default, must specify

Average_range

📍 Third argument in AVERAGEIF or second argument in AVERAGEIFS

Cells containing numbers to average

Default: Same as range if omitted in AVERAGEIF

Common Mistakes

Using AVERAGEIF with multiple conditions separated by commas

AVERAGEIF only supports one condition; multiple conditions require AVERAGEIFS

Use AVERAGEIFS for multiple conditions, listing each condition separately

Not putting text criteria in quotes

Text criteria must be in quotes for the formula to understand it as text

Always put text criteria inside double quotes, like "Apples"

Using different sized ranges for criteria and average_range

Ranges must be the same size or the formula returns an error

Make sure the range and average_range cover the same number of rows or columns

Summary

AVERAGEIF calculates the average of numbers that meet one condition.

AVERAGEIFS calculates the average of numbers that meet multiple conditions.

Always use quotes for text conditions and keep ranges the same size.

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