SUMIF and SUMIFS 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 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 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	1300
2024-03-15	South	Electronics	1100
2024-03-25	East	Furniture	900
2024-03-30	West	Electronics	1150

1

Step 1: Create a list of unique product categories in a new column, for example in column F starting at F2: Electronics, Clothing, Furniture.

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

Expected Result

Cells F2:F4 contain the product categories.

2

Step 2: Create a list of unique regions in a new row, for example in row 1 starting at G1: North, South, East, West.

Manually type the regions: North, South, East, West in cells G1, H1, I1, J1.

Expected Result

Cells G1:J1 contain the region names.

3

Step 3: Use SUMIFS formula to calculate total sales for Electronics in North region. Enter this formula in cell G2.

=SUMIFS(D$2:D$9, C$2:C$9, $F2, B$2:B$9, G$1)

Expected Result

Cell G2 shows 1200 (sum of Electronics sales in North).

4

Step 4: Copy the formula from G2 across to J2 and down to row 4 to fill the summary table for all product categories and regions.

Copy =SUMIFS(D$2:D$9, C$2:C$9, $F2, B$2:B$9, G$1) to range G2:J4.

Expected Result

Summary table filled with total sales by product category and region.

5

Step 5: Use SUMIF formula to calculate total sales for each product category regardless of region. Enter this formula in cell F5.

=SUMIF(C$2:C$9, F2, D$2:D$9)

Expected Result

Cell F5 shows 4400 (total sales for Electronics).

6

Step 6: Copy the SUMIF formula down from F5 to F7 to get total sales for Clothing and Furniture.

Copy =SUMIF(C$2:C$9, F2, D$2:D$9) to cells F6 and F7.

Expected Result

Cells F5:F7 show total sales for Electronics, Clothing, and Furniture respectively.

Final Result

       North  South  East  West
Electronics 1200   1100   950   1150
Clothing    0      850    0     700
Furniture   1300   0      900   0

Total Sales by Category:
Electronics: 4400
Clothing: 1550
Furniture: 2200

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

✓North region has strong sales in Electronics and Furniture.

✓Clothing sales are highest in South and West regions.

Bonus Challenge

Add a date filter to calculate total sales only for February 2024 using SUMIFS.

Show Hint

Use SUMIFS with an additional condition on the Date column, for example: =SUMIFS(D$2:D$9, C$2:C$9, $F2, B$2:B$9, G$1, A$2:A$9, ">=2024-02-01", A$2:A$9, "<=2024-02-29")