AVERAGEIF and AVERAGEIFS in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to know the average sales amounts for different product categories and regions to understand where the company performs best.

📊 Data: You have a sales data table with columns: Date, Region, Product Category, Sales Amount.

🎯 Deliverable: Create a summary table showing average sales by product category and by region with conditions.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-05	North	Electronics	1200
2024-01-07	South	Clothing	800
2024-01-10	East	Electronics	950
2024-01-12	West	Clothing	700
2024-01-15	North	Clothing	650
2024-01-18	South	Electronics	1100
2024-01-20	East	Clothing	720
2024-01-22	West	Electronics	1300
2024-01-25	North	Electronics	1150
2024-01-28	South	Clothing	780

1

Step 1: Create a summary table with product categories in one column and regions in another column to organize your averages.

No formula needed for this step.

Expected Result

A table with rows for Electronics and Clothing, and columns for North, South, East, West.

2

Step 2: Calculate the average sales for Electronics category using AVERAGEIF.

=AVERAGEIF(C2:C11, "Electronics", D2:D11)

Expected Result

Average sales for Electronics is 1140.

3

Step 3: Calculate the average sales for Clothing category using AVERAGEIF.

=AVERAGEIF(C2:C11, "Clothing", D2:D11)

Expected Result

Average sales for Clothing is 730.

4

Step 4: Calculate the average sales for the North region using AVERAGEIF.

=AVERAGEIF(B2:B11, "North", D2:D11)

Expected Result

Average sales for North region is 1000.

5

Step 5: Calculate the average sales for the South region using AVERAGEIF.

=AVERAGEIF(B2:B11, "South", D2:D11)

Expected Result

Average sales for South region is 893.33.

6

Step 6: Calculate the average sales for Electronics in the East region using AVERAGEIFS.

=AVERAGEIFS(D2:D11, C2:C11, "Electronics", B2:B11, "East")

Expected Result

Average sales for Electronics in East is 950.

7

Step 7: Calculate the average sales for Clothing in the West region using AVERAGEIFS.

=AVERAGEIFS(D2:D11, C2:C11, "Clothing", B2:B11, "West")

Expected Result

Average sales for Clothing in West is 700.

Final Result

Summary Table

+----------------+--------+--------+-------+-------+
| Product/Region | North  | South  | East  | West  |
+----------------+--------+--------+-------+-------+
| Electronics    | 1175   | 1100   | 950   | 1300  |
| Clothing       | 650    | 763.33  | 720   | 700   |
+----------------+--------+--------+-------+-------+

Overall Average Sales:
- Electronics: 1140
- Clothing: 730
- North Region: 1000
- South Region: 893.33

✓Electronics sales are generally higher than Clothing sales.

✓West region has the highest average sales for Electronics.

✓East region has the lowest average sales among regions.

✓Using AVERAGEIFS helps find averages with multiple conditions like category and region.

Bonus Challenge

Create a formula to find the average sales for Clothing in the South region only for sales above 750.

Show Hint

Use AVERAGEIFS with three conditions: Product Category = Clothing, Region = South, Sales Amount > 750.

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