Value aggregation (SUM, COUNT, AVG) in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a summary report showing total sales, number of sales, and average sale amount for each product category.

📊 Data: You have a sales data table with columns: Date, Product Category, Product Name, and Sale Amount.

🎯 Deliverable: Create a summary table that shows for each Product Category: Total Sales, Number of Sales, and Average Sale Amount.

Sample Data

Date	Product Category	Product Name	Sale Amount
2024-06-01	Electronics	Headphones	120
2024-06-02	Electronics	Smartphone	800
2024-06-03	Home	Blender	60
2024-06-04	Home	Vacuum Cleaner	150
2024-06-05	Clothing	T-Shirt	25
2024-06-06	Clothing	Jeans	50
2024-06-07	Electronics	Tablet	300
2024-06-08	Home	Toaster	40
2024-06-09	Clothing	Jacket	100
2024-06-10	Electronics	Camera	450

1

Step 1: List unique product categories in a new column to prepare for summary.

In a new column, enter: =UNIQUE(B2:B11)

Expected Result

A list of unique categories: Electronics, Home, Clothing

2

Step 2: Calculate total sales for each product category using SUMIF.

Next to each category, enter: =SUMIF(B$2:B$11, E2, D$2:D$11)

Expected Result

Electronics total: 1670, Home total: 250, Clothing total: 175

3

Step 3: Count number of sales for each product category using COUNTIF.

Next to total sales, enter: =COUNTIF(B$2:B$11, E2)

Expected Result

Electronics count: 4, Home count: 3, Clothing count: 3

4

Step 4: Calculate average sale amount for each product category using AVERAGEIF.

Next to count, enter: =AVERAGEIF(B$2:B$11, E2, D$2:D$11)

Expected Result

Electronics average: 417.5, Home average: 83.33, Clothing average: 58.33

Final Result

Product Category | Total Sales | Number of Sales | Average Sale Amount
-----------------|-------------|-----------------|--------------------
Electronics      | 1670        | 4               | 417.5
Home             | 250         | 3               | 83.33
Clothing         | 175         | 3               | 58.33

✓Electronics has the highest total sales and average sale amount.

✓Home category has moderate sales with fewer transactions.

✓Clothing has the lowest total and average sales among the categories.

Bonus Challenge

Create a chart that visually compares total sales for each product category.

Show Hint

Use Insert > Chart, select the summary table, and choose a column chart for clear comparison.