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.