Value fields and aggregation in Excel - 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 and average sales per product category for the last quarter.

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

🎯 Deliverable: Create a summary table that shows each Product Category with the total sales amount and average sales amount.

Sample Data

Date	Product Category	Product Name	Units Sold	Sales Amount
2024-01-05	Electronics	Headphones	10	500
2024-01-15	Electronics	Smartphone	5	2500
2024-02-10	Home Appliances	Blender	8	400
2024-02-20	Home Appliances	Microwave	3	900
2024-03-05	Furniture	Desk	2	600
2024-03-15	Furniture	Chair	6	300
2024-03-25	Electronics	Tablet	4	1200
2024-03-30	Home Appliances	Toaster	7	350

1

Step 1: Create a list of unique Product Categories in a new column starting at cell G2.

In G2, enter: =UNIQUE(B2:B9)

Expected Result

G2:G4 will show Electronics, Home Appliances, Furniture

2

Step 2: Calculate total sales amount for each Product Category next to the unique list in column H.

In H2, enter: =SUMIF(B$2:B$9, G2, E$2:E$9) and copy down to H4

Expected Result

H2=4200, H3=1650, H4=900

3

Step 3: Calculate average sales amount per transaction for each Product Category in column I.

In I2, enter: =AVERAGEIF(B$2:B$9, G2, E$2:E$9) and copy down to I4

Expected Result

I2=1400, I3=550, I4=450

4

Step 4: Format the summary table with headers in row 1: G1='Product Category', H1='Total Sales', I1='Average Sales'.

Manually type headers in G1, H1, and I1.

Expected Result

Headers appear above the summary data.

Final Result

Product Category | Total Sales | Average Sales
------------------------------------------------
Electronics      | 4200        | 1400
Home Appliances  | 1650        | 550
Furniture        | 900         | 450

✓Electronics has the highest total sales and average sales per transaction.

✓Home Appliances has moderate sales with average sales lower than Electronics.

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

Bonus Challenge

Add a column to calculate the total units sold per Product Category.

Show Hint

Use SUMIF with the Units Sold column similar to total sales calculation.