SUM function in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales assistant at a retail store.

📋 Request: Your manager wants you to calculate the total sales for each product category and the overall total sales for the month.

📊 Data: You have a table listing sales transactions with columns for Product Category, Product Name, and Sales Amount.

🎯 Deliverable: Create a summary table that shows total sales per product category and the grand total sales.

Sample Data

Product Category	Product Name	Sales Amount
Electronics	Headphones	120
Electronics	Smartphone	450
Clothing	T-Shirt	35
Clothing	Jeans	80
Home	Blender	60
Home	Toaster	40
Electronics	Camera	300
Clothing	Jacket	120

1

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

Manually type Electronics, Clothing, Home in cells E2, E3, and E4.

Expected Result

Cells E2:E4 contain Electronics, Clothing, Home.

2

Step 2: Calculate total sales for Electronics category in cell F2 using SUM and IF.

=SUM(IF(A2:A9=E2,C2:C9,0)) entered as an array formula (Ctrl+Shift+Enter).

Expected Result

F2 shows 870 (120+450+300).

3

Step 3: Calculate total sales for Clothing category in cell F3 using SUM and IF.

=SUM(IF(A2:A9=E3,C2:C9,0)) entered as an array formula (Ctrl+Shift+Enter).

Expected Result

F3 shows 235 (35+80+120).

4

Step 4: Calculate total sales for Home category in cell F4 using SUM and IF.

=SUM(IF(A2:A9=E4,C2:C9,0)) entered as an array formula (Ctrl+Shift+Enter).

Expected Result

F4 shows 100 (60+40).

5

Step 5: Calculate the grand total sales in cell F5 by summing all sales amounts.

=SUM(C2:C9)

Expected Result

F5 shows 1205 (sum of all sales).

Final Result

Product Category | Total Sales
-----------------|------------
Electronics      | 870
Clothing         | 235
Home             | 100
-----------------|------------
Grand Total      | 1205

✓Electronics is the highest sales category with 870 in sales.

✓Clothing sales total 235, and Home sales total 100.

✓Overall, the store made 1205 in sales this month.

Bonus Challenge

Use the SUMIF function to calculate total sales per category instead of SUM with IF.

Show Hint

Use formula like =SUMIF(A2:A9, E2, C2:C9) in cell F2 and copy down.