SUMIF and SUMIFS in Excel - 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 by product category and by region for the last quarter.

📊 Data: You have a sales data table with columns: Date, Region, Product Category, Sales Amount.

🎯 Deliverable: Create a summary table that shows total sales for each product category and total sales for each region using SUMIF and SUMIFS formulas.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-05	North	Electronics	1200
2024-01-15	South	Clothing	850
2024-02-10	East	Electronics	950
2024-02-20	West	Clothing	700
2024-03-05	North	Furniture	1500
2024-03-15	South	Electronics	1100
2024-03-25	East	Furniture	1300
2024-03-30	West	Electronics	900

1

Step 1: Create a list of unique product categories in a new column to prepare for total sales calculation.

Manually type the product categories: Electronics, Clothing, Furniture in cells F2, F3, F4.

Expected Result

Cells F2:F4 contain Electronics, Clothing, Furniture.

2

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

=SUMIF(C:C, F2, D:D) (copy down for F3 and F4)

Expected Result

Electronics total sales = 1200 + 950 + 1100 + 900 = 4150; Clothing = 850 + 700 = 1550; Furniture = 1500 + 1300 = 2800.

3

Step 3: Create a list of unique regions in a new column to prepare for total sales calculation.

Manually type the regions: North, South, East, West in cells H2, H3, H4, H5.

Expected Result

Cells H2:H5 contain North, South, East, West.

4

Step 4: Calculate total sales for each region using SUMIF.

=SUMIF(B:B, H2, D:D) (copy down for H3:H5)

Expected Result

North total sales = 1200 + 1500 = 2700; South = 850 + 1100 = 1950; East = 950 + 1300 = 2250; West = 700 + 900 = 1600.

5

Step 5: Calculate total sales for Electronics in the North region using SUMIFS.

=SUMIFS(D:D, B:B, "North", C:C, "Electronics")

Expected Result

Total sales = 2100.

6

Step 6: Calculate total sales for Clothing in the South region using SUMIFS.

=SUMIFS(D:D, B:B, "South", C:C, "Clothing")

Expected Result

Total sales = 850.

Final Result

Product Category Sales Summary
-----------------------------
Electronics: 4150
Clothing: 1550
Furniture: 2800

Region Sales Summary
-------------------
North: 2700
South: 1950
East: 2250
West: 1600

Example SUMIFS Results
---------------------
Electronics in North: 2100
Clothing in South: 850

✓Electronics is the top-selling product category with total sales of 4150.

✓North region has the highest sales among regions with 2700 total sales.

✓SUMIFS formulas help find sales for specific product and region combinations.

Bonus Challenge

Create a summary table that shows total sales by product category for each region using SUMIFS.

Show Hint

Use SUMIFS with region and product category as criteria, and arrange results in a grid with regions as columns and product categories as rows.

Practice

1. What does the SUMIF function do in Excel?

easy

A. Adds numbers in a range that meet one condition

B. Adds numbers in multiple ranges without conditions

C. Counts how many cells meet a condition

D. Finds the average of numbers in a range

Solution

1. Step 1: Understand the purpose of SUMIF

   SUMIF adds values only if they meet a single specified condition.

2. Step 2: Compare with other options

   Counting cells or averaging are different functions; SUMIF sums conditionally.

3. Final Answer:

   Adds numbers in a range that meet one condition -> Option A

4. Quick Check:

   SUMIF = sum with one condition [OK]

Hint: SUMIF sums with one condition, SUMIFS with many [OK]

Common Mistakes:

• Confusing SUMIF with COUNTIF
• Thinking SUMIF works with multiple conditions
• Using SUMIF to average values

2. Which of these is the correct syntax for the SUMIFS function?

easy

A. SUMIFS(criteria_range1, criteria1, sum_range)

B. SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

C. SUMIFS(sum_range, criteria1, criteria_range1)

D. SUMIFS(sum_range, criteria_range1, criteria1)

Solution

1. Step 1: Recall SUMIFS syntax

   SUMIFS requires sum_range first, then pairs of criteria_range and criteria.

2. Step 2: Check each option

   Only SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) follows the correct order: sum_range, criteria_range1, criteria1, etc.

3. Final Answer:

   SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) -> Option B

4. Quick Check:

   SUMIFS syntax = sum_range + criteria pairs [OK]

Hint: SUMIFS starts with sum_range, then criteria pairs [OK]

Common Mistakes:

• Swapping sum_range and criteria_range
• Missing criteria pairs
• Using SUMIF syntax for SUMIFS

3. Given this data:

A1:A5 = {10, 20, 30, 40, 50}
B1:B5 = {"Yes", "No", "Yes", "No", "Yes"}

What is the result of =SUMIF(B1:B5, "Yes", A1:A5)?

medium

A. 150

B. 100

C. 120

D. 90

Solution

1. Step 1: Identify matching cells in B1:B5

   Cells with "Yes" are B1, B3, B5 corresponding to A1=10, A3=30, A5=50.

2. Step 2: Sum corresponding values in A1:A5

   Sum = 10 + 30 + 50 = 90.

3. Final Answer:

   90 -> Option D

4. Quick Check:

   Sum of A where B = "Yes" is 90 [OK]

Hint: Sum values where criteria matches in criteria_range [OK]

Common Mistakes:

• Summing all values ignoring criteria
• Confusing Yes and No cells
• Using SUM instead of SUMIF

4. You wrote this formula: =SUMIFS(A1:A5, B1:B5, "Yes", C1:C5, ">10") but it returns #VALUE!. What is the likely problem?

medium

A. Sum range and criteria ranges are different sizes

B. SUMIFS cannot handle multiple criteria

C. Criteria ">10" should be in quotes

D. You must use SUMIF, not SUMIFS

Solution

1. Step 1: Check criteria syntax

   Criteria ">10" is correctly in quotes, so no error there.

2. Step 2: Verify range sizes

   SUMIFS requires sum_range and all criteria_ranges to be the same size; mismatch causes #VALUE! error.

3. Final Answer:

   Sum range and criteria ranges are different sizes -> Option A

4. Quick Check:

   Range size mismatch causes SUMIFS to return #VALUE! [OK]

Hint: Ensure all ranges in SUMIFS have same size [OK]

Common Mistakes:

• Forgetting to quote criteria with operators
• Using SUMIF syntax for multiple criteria
• Assuming SUMIFS sums without matching sizes

5. You have sales data:

Product: A2:A7 = {"Pen", "Pen", "Book", "Pen", "Book", "Book"}
Region: B2:B7 = {"East", "West", "East", "East", "West", "East"}
Sales: C2:C7 = {100, 200, 150, 120, 180, 130}

Which formula sums sales of "Pen" in the "East" region?

hard

A. =SUMIFS(A2:A7, C2:C7, "Pen", B2:B7, "East")

B. =SUMIF(A2:A7, "Pen", C2:C7) + SUMIF(B2:B7, "East", C2:C7)

C. =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East")

D. =SUMIF(C2:C7, "Pen", B2:B7)

Solution

1. Step 1: Identify correct function for multiple conditions

   SUMIFS sums with multiple criteria; SUMIF only handles one.

2. Step 2: Check formula arguments

   =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East") correctly uses sum_range C2:C7, criteria ranges A2:A7 and B2:B7 with criteria "Pen" and "East".

3. Final Answer:

   =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East") -> Option C

4. Quick Check:

   SUMIFS with product and region criteria sums sales [OK]

Hint: Use SUMIFS for multiple conditions, not SUMIF [OK]

Common Mistakes:

• Adding two SUMIF results instead of using SUMIFS
• Mixing sum_range and criteria_range order
• Using SUMIF with multiple criteria