SUMIF and SUMIFS in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate total sales for a specific product

You have a list of sales in column A (Product names) and amounts in column B. Which formula correctly sums sales only for the product named "Apple"?

A =SUMIF(A2:A10, "Apple", B2:B10)

B =SUMIF(B2:B10, "Apple", A2:A10)

C =SUMIF(A2:A10, B2:B10, "Apple")

D =SUMIF("Apple", A2:A10, B2:B10)

💡 Hint

Remember, SUMIF sums values in the sum range where the criteria range matches the condition.

The correct formula is =SUMIF(A2:A10, "Apple", B2:B10). It checks the product names in A2:A10 for "Apple" and sums the corresponding amounts in B2:B10.

Other options either swap ranges or use wrong argument order, causing errors or wrong results.

📊 Formula Result

intermediate

Sum sales for a product with quantity greater than 5

You have product names in A2:A15, quantities sold in B2:B15, and sales amounts in C2:C15. Which formula sums sales amounts only for "Banana" where quantity sold is more than 5?

A =SUMIF(A2:A15, "Banana", C2:C15) + SUMIF(B2:B15, ">5", C2:C15)

B =SUMIFS(C2:C15, A2:A15, "Banana", B2:B15, ">5")

C =SUMIFS(C2:C15, A2:A15, ">5", B2:B15, "Banana")

D =SUMIFS(A2:A15, C2:C15, "Banana", B2:B15, ">5")

💡 Hint

SUMIFS sums values in the first range where all criteria match in pairs.

Option B is correct because it sums sales amounts in C2:C15 where product is "Banana" and quantity in B2:B15 is greater than 5.

Option B adds separate sums incorrectly. Option B uses wrong sum_range and criteria ranges. Option B mismatches criteria: ">5" on products and "Banana" on quantities, yielding no results.

❓ Function Choice

advanced

Choose the formula to sum sales for dates in January 2024

You have sales dates in A2:A50 and sales amounts in B2:B50. Which formula sums sales only for dates in January 2024?

A =SUMIFS(B2:B50, A2:A50, ">=2024-01-01", A2:A50, "<=2024-01-31")

B =SUMIFS(B2:B50, A2:A50, ">=DATE(2024,1,1)", A2:A50, "<=DATE(2024,1,31)")

C =SUMIFS(B2:B50, A2:A50, ">="&DATE(2024,1,1), A2:A50, "<="&DATE(2024,1,31))

D =SUMIF(A2:A50, ">=DATE(2024,1,1)", B2:B50) - SUMIF(A2:A50, ">DATE(2024,1,31)", B2:B50)

💡 Hint

Use DATE function with & to concatenate criteria strings properly.

Option C is correct because it uses the DATE function inside the criteria with concatenation (&) to form valid criteria strings.

Option C and C treat dates as text without concatenation, causing criteria to be treated literally, so no matches.

Option C tries to subtract sums but uses DATE inside quotes without concatenation, so it fails.

❓ data_analysis

advanced

Find total sales for products starting with 'S' and quantity less than 10

You have product names in A2:A100, quantities in B2:B100, and sales amounts in C2:C100. Which formula sums sales for products whose names start with 'S' and quantity is less than 10?

A =SUMIFS(C2:C100, A2:A100, "S*", B2:B100, "<10")

B =SUMIF(A2:A100, "S*", C2:C100) + SUMIF(B2:B100, "<10", C2:C100)

C =SUMIFS(C2:C100, A2:A100, "S", B2:B100, "<10")

D =SUMIFS(C2:C100, A2:A100, "*S", B2:B100, "<10")

💡 Hint

Use wildcard * to match text starting with a letter.

Option A correctly uses "S*" to match any product starting with 'S' and quantity less than 10.

Option A matches products ending with 'S', not starting.

Option A matches exact 'S' only.

Option A sums separately and adds, which is incorrect because it sums all products starting with 'S' plus all quantities less than 10, not combined.

🎯 Scenario

expert

Analyze sales with multiple criteria including blanks

You have a sales table with columns: Product (A2:A200), Region (B2:B200), Sales (C2:C200). Some Region cells are blank. You want to sum sales for product "Orange" only in rows where Region is NOT blank. Which formula achieves this?

A =SUMIF(C2:C200, A2:A200, "Orange")

B =SUMIFS(C2:C200, A2:A200, "Orange")

C =SUMIFS(C2:C200, A2:A200, "Orange", B2:B200, "")

D =SUMIFS(C2:C200, A2:A200, "Orange", B2:B200, "<>")

💡 Hint

Use "<>" as criteria to exclude blank cells.

Option D is correct: sums "Orange" sales where Region is not blank using "<>".

Option D omits Region criteria, including blanks.

Option D uses "" criteria to include only blank Regions.

Option D (SUMIF) lacks Region criteria, including blanks.

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