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.

Practice

1. What does the SUM function do in Excel?

easy

A. Adds numbers or ranges to find a total

B. Subtracts numbers in a range

C. Finds the average of numbers

D. Counts the number of cells with numbers

Solution

1. Step 1: Understand the purpose of SUM

   The SUM function is designed to add numbers or ranges of cells.

2. Step 2: Compare with other functions

   Unlike subtraction, average, or count, SUM specifically adds values.

3. Final Answer:

   Adds numbers or ranges to find a total -> Option A

4. Quick Check:

   SUM adds values [OK]

Hint: SUM always adds numbers or ranges [OK]

Common Mistakes:

• Confusing SUM with AVERAGE
• Thinking SUM counts cells
• Using SUM to subtract

2. Which of the following is the correct syntax to sum cells A1 to A5?

easy

A. =SUM(A1:A5)

B. =SUM(A1-A5)

C. =SUM(A1;A5)

D. =SUM(A1+A5)

Solution

1. Step 1: Identify correct range syntax

   Excel uses colon (:) to specify a range from A1 to A5.

2. Step 2: Check each option

   =SUM(A1:A5) uses =SUM(A1:A5), which is correct. Others use invalid separators or operators.

3. Final Answer:

   =SUM(A1:A5) -> Option A

4. Quick Check:

   Range uses colon : [OK]

Hint: Use colon : to specify ranges in SUM [OK]

Common Mistakes:

• Using minus instead of colon
• Using semicolon instead of colon
• Adding cells inside SUM

3. Given the values in cells: A1=3, A2=7, A3=2, what is the result of =SUM(A1, A2, A3)?

medium

A. 0

B. 7

C. 3

D. 12

Solution

1. Step 1: Identify values in cells

   A1=3, A2=7, A3=2 are the numbers to add.

2. Step 2: Calculate the sum

   3 + 7 + 2 = 12

3. Final Answer:

   12 -> Option D

4. Quick Check:

   3+7+2=12 [OK]

Hint: Add all cell values inside SUM separated by commas [OK]

Common Mistakes:

• Adding only first two cells
• Confusing SUM with average
• Ignoring some cell values

4. The formula =SUM(A1:A3 B1:B3) gives an error. What is the fix?

medium

A. Replace colon with semicolon: =SUM(A1;A3 B1;B3)

B. Add a comma between ranges: =SUM(A1:A3, B1:B3)

C. Remove one range: =SUM(A1:A3)

D. Use plus sign between ranges: =SUM(A1:A3+B1:B3)

Solution

1. Step 1: Identify error cause

   Ranges must be separated by commas inside SUM; missing comma causes error.

2. Step 2: Correct the formula

   Insert comma between ranges: =SUM(A1:A3, B1:B3) fixes the syntax.

3. Final Answer:

   Add a comma between ranges: =SUM(A1:A3, B1:B3) -> Option B

4. Quick Check:

   Separate ranges with commas [OK]

Hint: Separate multiple ranges with commas in SUM [OK]

Common Mistakes:

• Missing commas between ranges
• Using spaces instead of commas
• Using plus sign inside SUM

5. You want to sum only the positive numbers in cells A1 to A5, where some cells have negative values. Which formula correctly sums only positive numbers?

hard

A. =SUM(A1:A5)

B. =SUMIF(A1:A5, "<0")

C. =SUMIF(A1:A5, ">0")

D. =SUM(A1:A5>0)

Solution

1. Step 1: Understand the requirement

   We want to add only positive numbers, ignoring negatives.

2. Step 2: Choose correct function

   SUMIF with condition ">0" sums only cells greater than zero.

3. Step 3: Check other options

   =SUM(A1:A5) sums all numbers, including negatives; C is invalid syntax; B sums negatives only.

4. Final Answer:

   =SUMIF(A1:A5, ">0") -> Option C

5. Quick Check:

   SUMIF with condition ">0" sums positives [OK]

Hint: Use SUMIF with ">0" to sum positives only [OK]

Common Mistakes:

• Using SUM to add all values
• Wrong condition in SUMIF
• Trying invalid syntax inside SUM