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.