Calculated fields in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a report showing total sales, total cost, and profit for each product category.

📊 Data: You have a sales data table with columns: Product, Category, Units Sold, Unit Price, and Unit Cost.

🎯 Deliverable: Create a summary table with Category, Total Sales, Total Cost, and Profit calculated using formulas.

Sample Data

Product	Category	Units Sold	Unit Price	Unit Cost
Shirt	Clothing	10	20	12
Jeans	Clothing	5	40	25
Hat	Accessories	8	15	7
Belt	Accessories	6	18	10
Notebook	Stationery	20	5	2
Pen	Stationery	50	2	1

1

Step 1: Create a list of unique product categories in a new column starting at G2.

=UNIQUE(B2:B7)

Expected Result

Clothing, Accessories, Stationery listed in cells G2, G3, G4

2

Step 2: Calculate Total Sales for each category in column H starting at H2. Total Sales = Units Sold * Unit Price summed by category.

=SUMPRODUCT((B$2:B$7=G2)*(C$2:C$7)*(D$2:D$7))

Expected Result

Clothing: 10*20 + 5*40 = 200 + 200 = 400 in H2

3

Step 3: Calculate Total Cost for each category in column I starting at I2. Total Cost = Units Sold * Unit Cost summed by category.

=SUMPRODUCT((B$2:B$7=G2)*(C$2:C$7)*(E$2:E$7))

Expected Result

Clothing: 10*12 + 5*25 = 120 + 125 = 245 in I2

4

Step 4: Calculate Profit for each category in column J starting at J2. Profit = Total Sales - Total Cost.

=H2 - I2

Expected Result

Clothing: 400 - 245 = 155 in J2

5

Step 5: Copy formulas in H2, I2, and J2 down for all categories in G3 and G4.

Copy formulas down to rows 3 and 4

Expected Result

Accessories Total Sales=8*15+6*18=120+108=228; Total Cost=8*7+6*10=56+60=116; Profit=112 Stationery Total Sales=20*5+50*2=100+100=200; Total Cost=20*2+50*1=40+50=90; Profit=110

Final Result

Category    Total Sales    Total Cost    Profit
Clothing        400           245         155
Accessories     228           116         112
Stationery      200            90         110

✓Clothing category has the highest total sales and profit.

✓Accessories have moderate sales and profit.

✓Stationery has lower sales but good profit margin.

Bonus Challenge

Add a new calculated field showing Profit Margin (%) for each category.

Show Hint

Use formula: Profit Margin = (Profit / Total Sales) * 100