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")

Practice

1. What does the SUMIF function do in Google Sheets?

easy

A. Adds numbers that meet one specific condition

B. Adds all numbers in a range regardless of conditions

C. Counts how many cells meet a condition

D. Finds the average of numbers that meet multiple conditions

Solution

1. Step 1: Understand the purpose of SUMIF

   The SUMIF function adds values only if they meet one condition you specify.

2. Step 2: Compare with other options

   Options A, B, and C describe different functions like AVERAGEIFS, SUM, and COUNTIF, not SUMIF.

3. Final Answer:

   Adds numbers that meet one specific 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
• Mixing SUMIF with AVERAGE functions

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

easy

A. SUMIFS(sum_range, criteria1, criteria_range1)

B. SUMIFS(criteria_range1, criteria1, sum_range)

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

D. SUMIFS(sum_range, criteria_range1, criteria1)

Solution

1. Step 1: Recall SUMIFS syntax

   The correct syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

2. Step 2: Check each option

   SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) matches the correct order: sum_range first, then pairs of criteria ranges and criteria. Others have wrong order or missing parts.

3. Final Answer:

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

4. Quick Check:

   SUMIFS = sum_range first, then criteria pairs [OK]

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

Common Mistakes:

• Putting criteria before sum_range
• Missing criteria_range or criteria pairs
• Using SUMIF syntax for SUMIFS

3. Given this data in columns A and B:

A1:A5 = {"Apple", "Banana", "Apple", "Orange", "Banana"}
B1:B5 = {10, 20, 30, 40, 50}

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

medium

A. 20

B. 70

C. 50

D. 30

Solution

1. Step 1: Identify matching rows for "Banana"

   In A1:A5, "Banana" appears in A2 and A5.

2. Step 2: Sum corresponding values in B1:B5

   Values at B2 and B5 are 20 and 50. Sum = 20 + 50 = 70.

3. Final Answer:

   70 -> Option B

4. Quick Check:

   Sum of Banana values = 70 [OK]

Hint: Sum values where criteria matches in same row [OK]

Common Mistakes:

• Summing only first match
• Adding values from wrong rows
• Confusing criteria with sum range

4. You wrote this formula: =SUMIFS(B1:B5, A1:A5, "Apple", C1:C5, ">10")
But column C is empty. What will happen?

medium

A. The formula returns 0 because no rows meet all conditions

B. The formula returns an error because C1:C5 is empty

C. The formula sums all values in B1:B5 ignoring conditions

D. The formula sums values where A1:A5 is "Apple" only

Solution

1. Step 1: Understand the conditions

   The formula sums B1:B5 where A1:A5 = "Apple" AND C1:C5 > 10.

2. Step 2: Check empty C1:C5

   Since C1:C5 is empty, no cells are > 10, so no rows match both conditions.

3. Final Answer:

   The formula returns 0 because no rows meet all conditions -> Option A

4. Quick Check:

   Empty criteria range means no matches, sum = 0 [OK]

Hint: Empty criteria range means no matches, sum is zero [OK]

Common Mistakes:

• Expecting an error from empty range
• Ignoring second condition
• Thinking it sums all "Apple" rows anyway

5. You have this data:

A1:A6 = {"East", "West", "East", "North", "West", "East"}
B1:B6 = {100, 200, 150, 300, 250, 50}
C1:C6 = {"Yes", "No", "Yes", "No", "Yes", "No"}

Which formula sums values in B where region is "East" and status is "Yes"?

hard

A. =SUMIFS(B1:B6, A1:A6, "East")

B. =SUMIF(A1:A6, "East", B1:B6) + SUMIF(C1:C6, "Yes", B1:B6)

C. =SUMIF(B1:B6, "East", C1:C6)

D. =SUMIFS(B1:B6, A1:A6, "East", C1:C6, "Yes")

Solution

1. Step 1: Understand the requirement

   We want to sum B values where A = "East" AND C = "Yes".

2. Step 2: Check each formula

   =SUMIFS(B1:B6, A1:A6, "East", C1:C6, "Yes") uses SUMIFS with both conditions correctly. =SUMIF(A1:A6, "East", B1:B6) + SUMIF(C1:C6, "Yes", B1:B6) sums separately and adds, which is incorrect. =SUMIFS(B1:B6, A1:A6, "East") misses second condition. =SUMIF(B1:B6, "East", C1:C6) has wrong argument order.

3. Final Answer:

   =SUMIFS(B1:B6, A1:A6, "East", C1:C6, "Yes") -> Option D

4. Quick Check:

   SUMIFS sums with multiple conditions [OK]

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

Common Mistakes:

• Adding separate SUMIF results instead of using SUMIFS
• Missing one condition in formula
• Using wrong argument order